Reputation: 324
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
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