apologies if this has already been answered although I have searched and search with no luck. in a nutshell im trying to change the cell colour if that cell value does not match a value in a named range.
I have tried a number of methods although none are working for me , any help from the vba gurus would be greatly appreciated.
essentially I have a list of values on sheet1(Create) G2:G5000 that I need to know when they don't match value on sheet2(lists) S2:S64 <--this has a named range of Make.
please see a copy of my current code below
Sub testMake()
Dim MkData As Range, MkVal As Range
Dim MKArray As Variant
Set MkData = Worksheets("Create").Range("G2:G5000")
Set MkVal = Worksheets("Lists").Range("Make")
For Each MyCell In MkData
If MyCell.Value <> Range("MkVal") Then
MyCell.Interior.ColorIndex = 6
Else
MyCell.Interior.ColorIndex = xlNone
End If
Next
End Sub
Thanks you all for any help in advance, I have been looking at this for a few days now and seem to be no closer than when I started.
You could be using Worksheet function Vlookup to compare between the two ranges:
Sub testMake()
Dim MkData As Range, MkVal As Range
Dim MKArray As Variant
Dim result As Variant
Set MkData = Worksheets("Create").Range("G2:G5000")
Set MkVal = Worksheets("Lists").Range("Make")
For Each MyCell In MkData
On Error Resume Next
result = Application.WorksheetFunction.VLookup(MyCell, MkVal, 1, False)
If Err <> 0 Then
result = CVErr(xlErrNA)
End If
If Not IsError(result) Then
MyCell.Interior.ColorIndex = xlNone
Else
MyCell.Interior.ColorIndex = 6
End If
Next
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments