Highlight Cell with Certain Value (VBA)

[gripper]

I have a workbook and what I have been using to highlight cells is Conditional Formatting. The issue I have now is when I copy this to another sheet with other data the conditional formatting is conflicting with other items on that new sheet so I need to get away from that and just use highlighting in its basic form.

What I want to do is the following:

Column X through Column AF any cell within these columns with a value of greater than zero I want to highlight orange.
This will be from Row 2 to last Row in Each column. (Each column has the same number of rows).

In my macro I currently have a last row variable as "lrow = Cells(Rows.Count, "B").End(xlUp).Row"

I thank you in advance for your assistance.

 

[Yongle]

Code below
- loops all cells in the active sheet in columns X to AF, rows 2 to lRow
- identifies relevant cells and puts them in one range (using UNION function)
- clears previous interior cell formatting in X to AF
- colours relevant cells

Sub Highlight()
    Dim lRow As Long, Cel As Range, Data As Range, Orange As Range
    With ActiveSheet
        lRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Data = .Range("X2:AF" & lRow)
        For Each Cel In Data
            If IsNumeric(Cel) Then
                If Cel > 0 Then
                    If Not Orange Is Nothing Then Set Orange = Union(Orange, Cel) Else Set Orange = Cel
                End If
            End If
        Next Cel
        Data.Interior.Color = xlNone
        If Not Orange Is Nothing Then Orange.Interior.Color = 49407
    End With
End Sub

 

[gripper]

Yongle,

Thank you so much for taking the time to post a reply. This code works perfectly.

Again, Thank you