Excel 2007 : Highlighting Specific Collumns Macro

**shep123** · 06-04-2012, 10:14 AM

I am trying to use a macro that will highlight columns E and I of the rows that collumn L equals "XXXXXXX." How can I designate the columns to tie into the row?

Sub highlight()

Dim rg As Range, c As Range
  Dim firstAddress As String
    Set rg = Range("L9", "L5000")
    Application.ScreenUpdating = False
With rg
    Set c = .Find("XXXXXXX", lookat:=xlWhole, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Columns.Range("E", "I").Interior.ColorIndex = 4 "ISSUE IS HERE
            
            Set c = .FindNext(c)
            If c Is Nothing Then Exit Do
        Loop While c.Address <> firstAddress
    End If
Application.ScreenUpdating = True
End With

End Sub

**rvasquez** · 06-04-2012, 10:34 AM

Here you go, this should fix it!

Sub highlight()

Dim rg As Range, c As Range
Dim firstAddress As String
Set rg = Range("L9", "L5000")
Application.ScreenUpdating = False
With rg
Set c = .Find("XXXXXXX", lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'column I is -3 columns from L
c.Offset(0, -3).Interior.ColorIndex = 4

'column I is -7 columns from L
c.Offset(0, -7).Interior.ColorIndex = 4

Set c = .FindNext(c)
If c Is Nothing Then Exit Do
Loop While c.Address <> firstAddress
End If
Application.ScreenUpdating = True
End With

End Sub

Let me know if this works for you

**shep123** · 06-04-2012, 11:01 AM

Yes that worked. Thanks for the help!

**vandan_tanna** · 06-04-2012, 10:48 AM

I think using conditional formatting in this case will be a lot easier than VBA.

I would create conditional formatting using row relative cell reference for columns E and I and then copy it down.

for example for E5: if $L5 = "XXXXXX" then apply conditional format (note missing "$" sign before 5)

**jeffreybrown** · 06-04-2012, 11:01 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here