Excel VBA: isna isin procedure enhancements

You may try something like this...

Sub Is_In()
Dim UserRange As Range, ComparisonRange As Range
Application.DisplayAlerts = False

Dim c As Range
On Error GoTo exit_routine_isin
Set UserRange = Application.InputBox(prompt:="Please Select your range source, in this range you will find the cells wich are 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)

If UserRange.Row <> ComparisonRange.Row Then
    MsgBox "The row#" & ComparisonRange.Row & " of Comparison Range is not equal to the row#" & UserRange.Row & " of User Range." & vbNewLine & vbNewLine & _
            "Please make sure to select the same row for User Range and Comparison Range.", vbExclamation
    Exit Sub
End If

For Each c In UserRange
If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
c.EntireRow.Interior.ColorIndex = "4"
End If
Next c
exit_routine_isin:
End Sub

Select allOpen in new window

Same way you can tweak the other code.

Hi,

Add a test checking if each range hold the same amount of rows before processing further:

If(UserRange.Rows.Count = ComparisonRange.Rows.Count) Then
    '// data processing
End If

Select allOpen in new window

Thank you for those proposal. I think I didn't specify properly the needs.
 What I want is to speed up the process.
Instead of asking reporting the range to select for both ranges: Example ("A2:A4000"),  I prefer to ask in inputbox enter the initial range Example A2 and then the procedure should be able to select last range related to initial range. No need to add the control between both ranges.
After entering initial range in both inputbox I would like to have Msgbox which display the whole range (Initial Range:FinalRange).
And finalize the procedure by applying filter color related to rows that has been highlighted by the formula.

Here is an example of another procedure that I have:

Set myRng = Application.InputBox("Select your first cell", "Cell of origin", Type:=8)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "The range reported is not valid"
Exit Sub
End If
Set myRng = Range(myRng, myRng.SpecialCells(xlCellTypeLastCell))

Select allOpen in new window

Thank you again for your help.

Great, thank you for this.

and for applying the colour filter for cells related to:

c.EntireRow.Interior.ColorIndex = "4"

Select allOpen in new window

That line will highlight the whole row for matching cells in the UserRange.
If you only want to highlight the cells only in the UserRange, you may replace that line with the following line...

c.Interior.ColorIndex = 4

Select allOpen in new window

Got it, what I meant is to perform a color filter at the end of the procedure in order to show up easily the rows highlighted. Such as add a filter after line 17 like this:

UserRange1
Selection.AutoFilter
ActiveSheet. UserRange2.AutoFilter Field:=1, Criteria1:=.....
Operator:=xlFilterCellColor

Sub Is_In()
Dim UserRange As Range, ComparisonRange As Range
Application.DisplayAlerts = False

Dim c As Range
On Error GoTo exit_routine_isin
Set UserRange1 = Application.InputBox("Select your first cell", "First Cell In Original Range", Type:=8)
Set ComparisonRange = Application.InputBox("Select your first cell", "First Cell In Comparison Range.", Type:=8)

Set UserRange2 = Range(UserRange1, Cells(Rows.Count, UserRange.Column).End(xlUp))
Set ComparisonRange = Range(ComparisonRange, Cells(Rows.Count, ComparisonRange.Column).End(xlUp))

For Each c In UserRange2
    If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
        c.EntireRow.Interior.ColorIndex = "4"
    End If
Next c
exit_routine_isin:
End Sub

Select allOpen in new window

Thank you very much for your help.

After the For Next loop, you can put the following line to filter the data based on Green color...

UserRange1.AutoFilter Field:=1, Criteria1:=vbGreen, Operator:=xlFilterCellColor

Select allOpen in new window

Got it. I will test it tomorrow.

Tested and it works! Thank you very much for your help.

You're welcome!