Don't highlight header row

[Jak]

Hi, I am highlighting cells with values that contain non numeric values or the numeric values that are greater or less than 10 digits in the column. The code does this. However, the numeric check does not stop at row 2. It picks up the header row which is text and highlights it. I can get around this by changing .End(xlUp).Row to .End(xlDown).Row in the code but it adds time to the code execution. Any pointers on getting the code to stop at row 2 would be welcomed.

Dim myRange As Range, lr As Long
lr = Cells(Rows.Count, "H").End(xlUp).Row

Set myRange = Range("H2:H" & lr)
For Each cell In myRange
If Len(Trim(cell)) > 0 Then
If IsNumeric(cell) And Len(cell) <> 10 Then cell.Interior.ColorIndex = 6
If Not IsNumeric(cell) Then cell.Interior.ColorIndex = 6
End If
Next cell

 

[Alex Blakenburg]

Unless I am missing something, it will only select the heading as part of the range if the column only contains the heading (and no data rows).
You can either enclose the code from Set down in an if statement eg

If lr > 1 Then 
' do the rest of the code
End if

or simply set lr to 2 if it returns a 1

lr = Cells(Rows.Count, "H").End(xlUp).Row
If lr = 1 Then lr = 2

 

[Jak]

lr = Cells(Rows.Count, "H").End(xlUp).Row If lr = 1 Then lr = 2

Thanks for the help Alex,
greatly appreciate it.

 

[Alex Blakenburg]

No problem. Glad I could help.