3
[Excel] Trying to check values in one column against a range of another column
Author Summary
plytheman is
in
Excel
Post Body
Hey all, another VBA Noob here. The short of it is I have a long column of locations in one sheet and another column with the correct location names on another sheet. I just want to highlight any locations in the first column (ActiveRange) that aren't in my second column (CheckRange). This is as far as I've gotten piecing together various examples online but when I ran it I got an "Error 9: Subscript out of Range".
Sub LocCheck()
Dim ActiveRange As Range
Dim CheckRange As Range
Dim myCell As Range
Set ActiveRange = Range("F6, F1000")
Set CheckRange = Worksheets("Sheet3").Range("A2, A50")
For Each myCell In ActiveRange
If WorksheetFunction.CountIf(CheckRange, myCell.Value) = 0 Then
myCell.Interior.ColorIndex = 36
End If
Next myCell
End Sub
Am I on the right track here? Thanks for looking!
Post Details
Location
We try to extract some basic information from the post title. This is not
always successful or accurate, please use your best judgement and compare
these values to the post title and body for confirmation.
- Posted
- 10 years ago
- Reddit URL
- View post on reddit.com
- External URL
- reddit.com/r/vba/comment...