Excel VBA: isna procedure & smart range selection

Hello experts,

I have the procedure reporting at the end of my message that I use from my personal.xlsb in order to compare values from different columns.
I would like to enhance the procedure by modifying the following:
1.      Instead of selecting the various range from top to down I would like to enter the column letter and the procedure should be able to display the range from initial top range till the last of used range and display input box “Range involved is equal to :”. This process should be applied for both columns
2.      Finish the procedure with the procedure by a filtering cells based on Interior.ColorIndex = "3"
3.      Display a Msgbox if the user cancels the procedure in of the range selection.
If you have questions, please contact me
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

Open in new window

LVL 1
LD16Asked:
Who is Participating?
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:
I guess you posted the incorrect code.
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...
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

With Range(Cells(1, colLetterSource), Cells(lr, colLetterSource))
    .AutoFilter field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
    If .SpecialCells(xlCellTypeVisible).Cells.Count <= 1 Then
        .AutoFilter field:=1
    End If
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

Open in new window

LD16Author Commented:
Great! Instead of using isna and isin in different procedures, we can have just one procedure with your proposal! Do you think it is possible to apply an order by colored filter to have green at the top and red at the button?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Like this?
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

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

LEARN MORE
LD16Author Commented:
Thank you very much for your this proposal.
I am having a problem when source range an comparison range is not in the same sheet.
Is it possible to set up source and comparison range in such a way to select them in different sheets?
I attached dummy file.
Thank you in advance for your help.
Is_In_Is_Na.xlsm
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Happy New Year! :)

Please find the attached and click the button on Source or TEST Sheet to invoke a  self explanatory UserForm and select either Source or TEST Sheet as the source sheet and hit the button Compare.
Is_In_Is_Na.xlsm
LD16Author Commented:
Happy New year! :)
Unable to test it right now. I will keep you posted!
Thank you again for your help.
LD16Author Commented:
This proposal is great however I would like not to use the user form and directly calling the procedure through a ui custom button which I use in my personal.xlsb. Do you think it is possible just to review the inputbox for source and comparison range in order to be able to move from one sheet to another to select the sheet. If this is not possible I can report the sheet name in the input box of each of it?
Thank you in advance for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why are you asking user to type column letter and the sheet name as that will be more work required from the user and would be prone to errors? Why not just asking them to select any cell in the source range and the target range? That way, they can activate the desired sheet and select any cell in the source and target ranges and the source sheet and the target sheet will be correctly picked by the code?
LD16Author Commented:
You are right, I think the best will be to ask column letter or select the initial range for both source and comparison range and display msgbox after the selection to see initial to end range.
I remember having the same problem with a previous questions related to sheet comparison:
https://www.experts-exchange.com/questions/29129536/

And it was solved with the following code
https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=20-42757251-1

Thank you in advance for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try the attached...
Is_In_Is_Na-v2.xlsm
LD16Author Commented:
Tested and it works, the full requirement was covered! Just one question I took a look related to the following variables:
wsTarget.Cells(2, Col) and wsSource.Cells(2, Col)
This means that inital range will start at row 2 no matter if there is blank space in row 2, is not a way to set up those variables to take into account the first no empty cell which will be the header + 1?

Thank you again for your help.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That assumes the row1 is the header row and the actual data starts from row2 for both the source and the target ranges.
LD16Author Commented:
Thank you for your feedback.
And if row1 is not the header but it should be row6 as row 1 to 5 is empty how we can identify the first no empty range?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, test this.
Is_In_Is_Na-v3.xlsm

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:
It works perfectly! Thank you again for your help!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked. 😊
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.