Excel VBA: isna isin procedure enhancements

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

LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 LambertConsultingCommented:
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

LD16Author 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.
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author 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 ExpertCommented:
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

LD16Author 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 ExpertCommented:
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

LD16Author Commented:
Got it. I will test it tomorrow.
LD16Author Commented:
Tested and it works! Thank you very much for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.