Run-time error '1004' when highlighting rows with certain values

[alyos]

Hi all,

I've looked at previous posts but still couldn't resolve the issue with a simple code, and would appreciate any navigations and advice - I'm beginner at this.

I work on a spreadsheet with only one worksheet, trying to come up with a decent way to highlight a row in a table ("Table1") that contains certain value (Conditional formatting is not something I want to use in this case).

Sub Highlight()

Dim count, i As Long

count = ActiveSheet.Cells(Rows.count, "A").End(xIUp).Row
i = 17
Do While i <= count
If Cells(i, 2).Value = "Transport" Then
Range(Cells(i, 1), Cells(i, 12)).Interior.Color = RGB(67, 195, 233)
End If
    i = i + 1
    Loop
    End Sub

When I debug, it highlights row 3 (count = ActiveSheet.Cells(Rows.count, "A").End(xIUp).Row)

Screenshots of a worksheet and code have been attached. There might be different ways of achieving this, anything would be of help!

Thank you for your time and help

 

[Alex Blakenburg]

Your xlUp has a capital i "I" instead of an lower case L "l"

 

[alyos]

Your xlUp has a capital i "I" instead of an lower case L "l"

Crickey! :D Thank you, well spotted... It works now (surprise-surprise).

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of other comments on your code

Dim count, i As Long

This only declares i as Long data type. count will a Variant data type.
To declare both as long you need

Dim count as Long, i As Long

It is a very bad idea to use a variable name the same as another word that already has a special meaning in vba (eg count)

 

[alyos]

Welcome to the MrExcel board!

A couple of other comments on your code

This only declares i as Long data type. count will a Variant data type.
To declare both as long you need

Dim count as Long, i As Long

It is a very bad idea to use a variable name the same as another word that already has a special meaning in vba (eg count)

Many thanks Peter for your tip, will incorporate this as well!

 

[Alex Blakenburg]

Glad we could help. Thanks for the feedback.

 

[Peter_SSs]

You're welcome.