Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

Excel VBA: Highlight values which don't exist

Hello experts,
I have the following procedure reported at:
https://www.experts-exchange.com/questions/29156896/
Sub Is_In()
    Dim wsSource As Worksheet
    Dim wsComaparison As Worksheet
    Dim rngSource As Range
    Dim ComparisonRange As Range
    Dim rCl As Range
    Dim LRSource As Long
    Dim LRComparison As Long
    Dim colSource As Long
    Dim colComparison As Long
    Dim cntMatch As Long
    
    Application.DisplayAlerts = False
    
    On Error Resume Next
    Set rngSource = Application.InputBox(Prompt:="Please Select any cell in your range source, in this range you will find the cells which are in your range to compare", Title:="Source Range Selection", Type:=8)
    
    Set ComparisonRange = Application.InputBox(Prompt:="Please Select any cell in the Range to compare", Title:="Select Range To Compare With", Type:=8)
    On Error GoTo 0
    
    If rngSource Is Nothing Then
        MsgBox "You didn't select any Source Range to compare.", vbExclamation
        Exit Sub
    ElseIf ComparisonRange Is Nothing Then
        MsgBox "You didn't select any Comparison Range to compare it with Source Range.", vbExclamation
        Exit Sub
    End If
    
    Set wsSource = rngSource.Parent
    Set wsComaparison = ComparisonRange.Parent
    
    colSource = rngSource.Column
    colComparison = ComparisonRange.Column
    
    LRSource = wsSource.Cells(Rows.Count, colSource).End(xlUp).Row
    LRComparison = wsComaparison.Cells(Rows.Count, colComparison).End(xlUp).Row
    
    Set rngSource = wsSource.Range(wsSource.Cells(2, colSource), wsSource.Cells(LRSource, colSource))
    Set ComparisonRange = wsComaparison.Range(wsComaparison.Cells(2, colComparison), wsComaparison.Cells(LRComparison, colComparison))
    
    If MsgBox("You are going to compare the range " & rngSource.Address(0, 0) & " on " & wsSource.Name & " Sheet with the range " & _
                ComparisonRange.Address(0, 0) & " on " & wsComaparison.Name & " Sheet." & vbNewLine & vbNewLine & _
              "Is that correct?", vbQuestion + vbYesNo, "Comfirm Please!") = vbNo Then

        MsgBox "You cancelled the range comparison.", vbExclamation, "Range Comparison Cancelled!"
        Exit Sub
    End If
    
    On Error GoTo Error_Routine
    
    For Each rCl In rngSource
        If IsNumeric(Application.Match(rCl, ComparisonRange, 0)) Then
            cntMatch = cntMatch + 1
            rCl.Interior.ColorIndex = "4"
        End If
    Next rCl
    
    MsgBox cntMatch & " values matched of total " & LRSource - 1 & " values.", vbExclamation
    
    Exit Sub
Error_Routine:
    MsgBox Err.Description, vbExclamation, "Something went wrong!"

    Application.DisplayAlerts = True

End Sub 

Open in new window

Which allows me to check values which exist based on another range.
I would like to apply the same process for the values which don't exist and highlight them with:
ColorIndex = "3"

Open in new window

If you have questions, please contact me.
Thank you for your help.
SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz

ASKER

Thank you very much Subodh.
Possible to have the approach by focusing  just on values which don't exist?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Tested and it works!
Thank you very much for your help!
You're welcome Luis!