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