Sub Is_Na()
Application.DisplayAlerts = False
Dim c As Range
On Error GoTo exit_routine_isna
Set UsedRange = Application.InputBox(prompt:="Please Select your range source, in this range you will find the cells wich are not in your range to compare", Title:="Range Select", Type:=8)
Set ComparisonRange = Application.InputBox(prompt:="Please Select Range to compare", Title:="Range Select", Type:=8)
For Each c In UsedRange
If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
Else: c.EntireRow.
End If
Next c Interior.ColorIndex = "3"
exit_routine_isna:
End Sub
Sub Is_Na()
Dim RngToCompare As Range, RngToComapreWith As Range, c As Range
Dim colLetterSource As String, colLetterTarget As String
Dim lr As Long
Application.ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
colLetterSource = Application.InputBox(prompt:="Please input the column letter of your source range. In this range you will find the cells wich are not in your range to compare", Type:=2)
If colLetterSource = "" Then
MsgBox "You didn't input the Column Letter for the Source Range.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterSource) Then
lr = Cells(Rows.Count, colLetterSource).End(xlUp).Row
Set RngToCompare = Range(Cells(2, colLetterSource), Cells(lr, colLetterSource))
If MsgBox("The Source Range involved is " & RngToCompare.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
colLetterTarget = Application.InputBox(prompt:="Please input the column letter of the Range to compare with.", Type:=2)
If colLetterTarget = "" Then
MsgBox "You didn't input the Column Letter for the Range to compare with.", vbExclamation
Exit Sub
Else
If ValidColumnLetter(colLetterTarget) Then
lr = Cells(Rows.Count, colLetterTarget).End(xlUp).Row
Set RngToComapreWith = Range(Cells(2, colLetterTarget), Cells(lr, colLetterTarget))
If MsgBox("The Source Range involved is " & RngToComapreWith.Address(0, 0) & vbNewLine & " Is this correct?", vbQuestion + vbYesNo, "Range To Compare!") = vbNo Then
MsgBox "You cancelled the action.", vbExclamation
Exit Sub
End If
Else
MsgBox "You entered an invalid column letter.", vbCritical
Exit Sub
End If
End If
For Each c In RngToCompare
If IsNumeric(Application.Match(c, RngToComapreWith, 0)) Then
c.EntireRow.Interior.ColorIndex = 3
Else
c.EntireRow.Interior.ColorIndex = 4
End If
Next c
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add(RngToCompare.Cells(1), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
ActiveSheet.Sort.SortFields.Add(RngToCompare.Cells(1), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
, 0)
With ActiveSheet.Sort
.SetRange Range("A1").CurrentRegion
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub
Function ValidColumnLetter(colLetter As String) As Boolean
Dim rng As Range
On Error Resume Next
Set rng = Range(colLetter & 1)
On Error GoTo 0
If Not rng Is Nothing Then ValidColumnLetter = True
End Function
In the code below if the value from Source Range is found in the Range To Compare, the entire row will be highlighted as green else it will be highlighted as red. If this logic is not correct, change the color index in the for loop.
You may try something like this...
Open in new window