CODEWITHSUNDEEP
vbaexcelrangecells
ygaft
ygaft

Reputation: 324

Find a table from a given cell excel-vba

I have a table which is located somewhere on a spreadsheet for example b3:f15 , and near it there is nothing ( empty cells ), I am trying to select this table and color it. Somehow my code doesn't work and I just can't find my logic mistake

Sub colorize_table()
   Dim temp As Range
    Set temp = Application.InputBox(prompt:="Please choose cell", Type:=8)
    Dim i As Integer, k As Integer
    Dim r As Integer, p As Integer
    i = temp.Row
    k = temp.Column
    r = temp.Row
    p = temp.Column
  While temp(i, k) <> ""
        While temp(i, k) <> ""
           i = i - 1
       Wend
        k = k - 1
    Wend
    While temp(r, p) <> ""
       While temp(r, p) <> ""
           r = r + 1
       Wend
       p = p + 1
   Wend
   Set temp = Range(Cells(i-1, k-1).Address, Cells(r+1, p+1).Address)
    temp.Interior.ColorIndex = 36
End Sub

Any suggestions? Thanks in advance

Upvotes: 0

Views: 82

Answers (2)

avb
avb

Reputation: 1753

Use: 

temp.CurrentRegion.Interior.ColorIndex = 36

instead of looping.

Upvotes: 3

Dan Wagner
Dan Wagner

Reputation: 2713

If your While/Wend loops are working, you can set the temp Range variable like this:

Set temp = Range(Cells(i-1, k-1), Cells(r+1, p+1)) '<~ no address needed

That being said, you're already prompting the user for an input -- could you ask him or her to enter the top-left cell in the table? From that your code could be much simplified...

Upvotes: 0

Related Questions