• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code to highlight column if not an EXACT Match - Not working

Bimmy

Member
Hi,

There are 2 sheets Budget Summary and Approved.

Column B in Budget Summary has a list of names. Column F in Approved is where names are pasted. The names are pasted from different sources.

I have a code that highlights Column F in Approved sheet, if data entered is not an Exact Match with data in Column B in Budget Summary.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim m As Long
    Dim n As Long
    m = Worksheets("Approved").Range("F" & Rows.Count).End(xlUp).Row
    n = Worksheets("Budget Summary").Range("B" & Rows.Count).End(xlUp).Row
    Range("F1:F" & m).Interior.ColorIndex = xlColorIndexNone
    For r = 1 To m
        If Evaluate("ISERROR(MATCH(TRUE,EXACT(Approved!F" & r & ",Budget Summary!$B$1:$B" & n & "),0))") Then
        Worksheets("Approved").Range("F" & r).Interior.Color = vbRed
        End If
    Next r
End Sub

The problem -

The code highlights column F even if the data pasted is an Exact Match.

I'm unable to figure out if there is anything wrong with the code.

Have attached sample sheet.

Kindly assist.



POST MOVED BY MODERATOR


.
 

Attachments

  • HL.xlsb
    19 KB · Views: 5
Last edited by a moderator:
You are trying to use array function in Evaluate (thus every iteration of loop ends up in ISERROR() = TRUE).

Instead, put it in out of way cell and use ".FormulaArray" to enter info and read from it.

But then, I prefer using RegEx and/or dictionary for exact match. Dinner time now, will see if I have time to look at it more in detail later.
 
You need to add apostrophe at beggining/end in sheet name if it contains space.
Code:
Evaluate("ISERROR(MATCH(TRUE,EXACT(Approved!F" & r & ",'Budget Summary'!$B$1:$B" & n & "),0))")
 
Hi, I was wondering why you need a macro to do this. I've attached an example where I use conditional formatting. You would need to make the named range and the CF-range dynamic to make it solid.
 

Attachments

  • Highlight Differences in 2 ranges.xlsx
    10.9 KB · Views: 1
i'm not familiar with RegEx, if it's not much of a trouble, can you provide me with the code.

On second thought, it's more efficient using just Dictionary alone in this instance, rather than using RegEx. Since Dictionary in itself can check for exact match using binary compare mode.

Here's code using Dictionary.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TA
Dim cel As Range
Dim i As Integer
TA = Worksheets("Budget Summary").[A1].CurrentRegion.Columns(2).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbBinaryCompare
    For i = 1 To UBound(TA)
        .Item(TA(i, 1)) = 1
    Next
    For Each cel In [F1].CurrentRegion.Columns(1).Cells
        If Not .Exists(cel.Value) Then
            cel.Interior.Color = vbRed
        End If
    Next
    .RemoveAll
End With
End Sub
 
Back
Top