Runtime Error - Object doesn't support this method

[fali34533]

Hi,

I am trying to cycle through the rows of the worksheet and if the value in Column D is London I want that row to be coloured.

However, I keep getting a runtime error and can't work out why. This is the code below:

Sub ColorRows()
Dim lastRow As Long
Dim i As Long
lastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
For i = 1 To lastRow
If ActiveSheet.Cells(i, "D").Value = "London" Then
ActiveSheet.Range("A" & i & ":BJ" & i).Interior.Color = RGB(254, 241, 207)
End If
Next i
End Sub

Any help would be much appreciated.

Thank you

 

[Alex Blakenburg]

You are not giveing us the full error message and you are not telling us what line is highlighted when you click on the Debug button.
Is your sheet protected by any chance ?
The code works fine for me.

 

[ipbr21054]

Is this correct ?
:BJ

("A" & i & ":BJ" & i)

 

[fali34533]

You are not giveing us the full error message and you are not telling us what line is highlighted when you click on the Debug button.
Is your sheet protected by any chance ?
The code works fine for me.

When I go into debug this line is highlighted

ActiveSheet.Range("A" & i & ":BJ" & i).Interior.Color = RGB(254, 241, 207)

 

[ipbr21054]

Like i mentioned.
Why is symbol BEFORE the BJ there ?

Try this

ActiveSheet.Range("A" & i & "BJ" & i).Interior.Color = RGB(254, 241, 207)

 

[Alex Blakenburg]

Like i mentioned.
Why is symbol BEFORE the BJ there ?

Try this

ActiveSheet.Range("A" & i & "BJ" & i).Interior.Color = RGB(254, 241, 207)

More importantly did you try what you are suggesting ? You will find it gives you a 1004 error object defined error. The original code is fine it is meant to produce A2:BJ2 (assuming i = 2) which is correct.

@fali34533
Did you copy in the code you have here or type it in ? Like I said before it works fine for me.
This is the same code you are using just using the VBA code button (which you need to be using too) and applying indents.

Sub ColorRows()
    Dim lastRow As Long
    Dim i As Long
    lastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
    For i = 1 To lastRow
        If ActiveSheet.Cells(i, "D").Value = "London" Then
            ActiveSheet.Range("A" & i & ":BJ" & i).Interior.Color = RGB(254, 241, 207)
        End If
    Next i
End Sub

The output from the macro was:

20230416 VBA RunTime Error fali34533.xlsm
	A	B	C	D	E	F	G
1				Col D	Col E	Col F
2				London
3				Adelaide
4				Perth
5				London
6				Sydney
7
Sheet1

 

[Alex Blakenburg]

Also you didn't answer the question as to whether your sheet was protected ?
Another consideration is do you have any merged cells ?