Highlight Cell with Certain Value (VBA)

gripper

Board Regular
Joined
Oct 29, 2002
Messages
143
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
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

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

Board Regular
Joined
Oct 29, 2002
Messages
143
Yongle,

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

Again, Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,114,187
Messages
5,546,457
Members
410,741
Latest member
Count25
Top