VBA - determine if a cell value (String) matches a Value (String) in a named range

1
votes

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.

2
excelrangestring-matchingnamedvba
Why don't use use conditional formatting (non-vba) for this? – brettdj

2 Answers

1
votes

While I would use conditional formatting you could slightly adapt your code as below to do this programatically:

Sub testMake()

Dim MkData As Range
Dim MkVal As Range
Dim MKArray As Variant
Dim lngRow As Long
Dim rng1 As Range
Dim rng2 As Range


MKArray = Worksheets("Create").Range("G2:G5000").Value2
Set rng1 = Worksheets("Create").Range("G2")

Set MkVal = Range("Make")

For lngRow = 1 To UBound(MKArray)
    If IsError(Application.Match(MKArray(lngRow, 1), MkVal, 0)) Then
        If Not rng2 Is Nothing Then
            Set rng2 = Union(rng2, rng1.Offset(lngRow - 1, 0))
            Else
            Set rng2 = rng1.Offset(lngRow - 1, 0)
        End If
    End If
Next

If Not rng2 Is Nothing Then rng2.Interior.ColorIndex = 6

End Sub
0
votes

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