Excel VBA: isna procedure & smart range selection

Luis Diaz
Luis Diaz used Ask the Experts™
on
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

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

Luis DiazIT consultant

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

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

Ensure you’re charging the right price for your IT

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!

Luis DiazIT consultant

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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
Luis DiazIT consultant

Author

Commented:
Happy New year! :)
Unable to test it right now. I will keep you posted!
Thank you again for your help.
Luis DiazIT consultant

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?
Luis DiazIT consultant

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Please try the attached...
Is_In_Is_Na-v2.xlsm
Luis DiazIT consultant

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
That assumes the row1 is the header row and the actual data starts from row2 for both the source and the target ranges.
Luis DiazIT consultant

Author

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?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay, test this.
Is_In_Is_Na-v3.xlsm
Luis DiazIT consultant

Author

Commented:
It works perfectly! Thank you again for your help!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked. 😊

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