Excel VBA: isna isin procedure enhancements

Luis Diaz
Luis Diaz used Ask the Experts™
on
UserRange and ComparisonRange should have the same row number otherwhise the procedure doesn’t work properly (the highlighted cells will be offset) this is why I prefer to ask “Select initial range cell” in both input box
 
If you have questions, please contact me.
Thank you very much for your feedback.

Sub Is_In()

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)


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




Sub Is_Na()

Application.DisplayAlerts = False

Dim c As Range
On Error GoTo exit_routine_isna
Set UserRange = 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 UserRange
If IsNumeric(Application.Match(c, ComparisonRange, 0)) Then
Else: c.EntireRow.Interior.ColorIndex = "3"
End If
Next c
exit_routine_isna:
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Open in new window


Same way you can tweak the other code.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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

Open in new window

Luis DiazIT consultant

Author

Commented:
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))

Open in new window


Thank you again for your help.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
If you want user to select the first cell for the UserRange and ComparisonRange only, you may try 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("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 UserRange = Range(UserRange, Cells(Rows.Count, UserRange.Column).End(xlUp))
Set ComparisonRange = Range(ComparisonRange, Cells(Rows.Count, ComparisonRange.Column).End(xlUp))

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

Open in new window

Luis DiazIT consultant

Author

Commented:
Great, thank you for this.

and for applying the colour filter for cells related to:

c.EntireRow.Interior.ColorIndex = "4"

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Open in new window

Luis DiazIT consultant

Author

Commented:
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

Open in new window


Thank you very much for your help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Open in new window

Luis DiazIT consultant

Author

Commented:
Got it. I will test it tomorrow.
Luis DiazIT consultant

Author

Commented:
Tested and it works! Thank you very much for your help.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial