Don't highlight header row

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
833
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.

VBA Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
6,073
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
If lr > 1 Then 
' do the rest of the code
End if

or simply set lr to 2 if it returns a 1
VBA Code:
lr = Cells(Rows.Count, "H").End(xlUp).Row
If lr = 1 Then lr = 2
 
Solution

Forum statistics

Threads
1,186,015
Messages
5,955,328
Members
438,192
Latest member
cem123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top