Luis Diaz
asked on
Excel VBA: Highlight values which don't exist
Hello experts,
I have the following procedure reported at:
https://www.experts-exchan ge.com/que stions/291 56896/
I would like to apply the same process for the values which don't exist and highlight them with:
Thank you for your help.
I have the following procedure reported at:
https://www.experts-exchan
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
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"
If you have questions, please contact me.Thank you for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tested and it works!
Thank you very much for your help!
Thank you very much for your help!
You're welcome Luis!
ASKER
Possible to have the approach by focusing just on values which don't exist?