VBA InStr

[gripper]

Hi

I am trying to create a VBA sub to highlight an entire row based on a partial string value in column "AK"
My error occurs at code line " If InStr(Cells(i, "AK").Value, "L-") Then "

I have a sheet with about 2000 rows of data and I run this reports and probably in 2 dozen of these rows there will be an entry in column "AK" that starts with "L-" and then a string of different numbers. I need to loop down the column and identify these records and highlight the row.

I searched the web before posting and it seems like my code is OK but I am obviously off with my syntax or approach all together.

Thank you for the help.

sub highlightRow()

Dim lrow, i As Long 
lrow = Cells(Rows.Count, "B").End(xlUp).Row
i = 2

Columns("AK:AK").Select
Do While i <= lrow
  
  If InStr(Cells(i, "AK").Value, "L-") Then
    Range(Cells(i, 1), Cells(i, 45)).Interior.Color = RGB(167, 220, 233)
 
  End If

  i = i + 1
 
  Loop

End Sub

 

[Saurabhj]

Hi Gripper,

Can you please share the sheet or data.

Thanks,
Saurabh

 

[Rick Rothstein]

What is the error number and error description that displays when the error occurs?

 

[gripper]

The error is "Run-time error '1004'
Application-defined or object-defined error

 

[gripper]

I figured it out. I forgot to set "i = 2" in my code block. This is part of a larger block and I was debugging and did not have the code capturing my initial "i" value before debugging that small section of code.

Amateur move on my part

 

[Rick Rothstein]

Okay, I'm glad you figured it out. I was about to write back to you "Hmm, that is not an error I would expect with the line of code you say it is occurring on".