I am trying to write Excel VBA code that will loop through the data in a column and check to see if the values are present in a column on another worksheet. I am doing this to validate that the data entered is from a value list. If the data is not present in the value list I want to set the background colour of the cell to red. The data is not entered into Excel so using a data validation list is not an option. Some values may be blank in the column that I am validating which is fine but I want to make sure it checks all rows where there is a value.

This is what I have cobbled together so far but it is changing the colour in the top row instead of going down column I.

Option Explicit

Sub Test()

Dim Rng As Range
Dim i As Long
Dim LastRow As Long

Set Rng = Worksheets("Lookups").Range("AC2:AC" & Range("AC" & Rows.Count).End(xlUp).Row)
Application.ScreenUpdating = False
With Worksheets("COMPANY")

    LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row

    For i = 1 To LastRow

        If Not IsError(Application.Match(.Range("I" & i).Value, Rng, 0)) Then
            .Cells(i).Interior.ColorIndex = 3
        End If
    Next i
End With
Application.ScreenUpdating = True

End Sub
  • 1
    Is there a reason you can't use conditional formatting? – cybernetic.nomad Jun 21 at 15:08
  • @cybernetic.nomad. This is part of something bigger that I was trying to do but perhaps I can just leave this part out of it and use conditional formatting... thanks for the suggestion – Gac_100 Jun 21 at 15:29
  • 1
    (1) What have you done to try to debug this? (2) Why do you use .Range("I" & i).Value to look at the value but .Cells(i) to set the color? – Scott Jun 22 at 2:25
  • @Gac_100, what I've realized that you are comparing two columns between Sheets for common values and try to apply BG color if No match/value,, and I'm sure you want to highlight Source Sheet's cells!! – Rajesh S Jun 22 at 5:05

This VBA (Macro) code will help you to Highlight all Rows is Sheet1 if corresponding values are not in sheet2.

Sub CompareSheets()

Dim i As Long, f As Variant

With Worksheets(1)
    For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
        f = Application.Match(.Cells(i, "A").Value2, Worksheets(2).Columns("A"), 0)
        If Not IsError(f) Then
            .Cells(i, "A").Interior.ColorIndex = 4
        End If
    Next i
End With
End Sub


  • The used Column is A form Row 1 onwards, you need to adjust reference as your need.
  • Sheet1 is the Source & Sheet2 is Target Sheet.
  • Your code might work, however writing it from scratch is less help for the OP understand issue in the original code. – Máté Juhász Jun 22 at 6:19
  • @MátéJuhász,, it's been tested, fully functional,, I've tried to show in simplest Form,, modify the original should be bit messy, basic points like Aplication.Match, use of Sheets & Color method are similar to OPs code,, and since OP is using VBA codes then it's simple to realize that OP can manage with the Code!! – Rajesh S Jun 22 at 7:37
  • ,,,Cont,, seems you have not tested the code !! – Rajesh S Jun 22 at 7:39
  • @RajeshS This worked! I just removed the Not from in front of IsError and it worked exactly as I needed. Thank you Rajesh! – Gac_100 Jun 24 at 14:32
  • @Gac_100, glad to help you ,, keep asking ☺ – Rajesh S Jun 25 at 3:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.