Luis Diaz

asked on 12/29/2018

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

VBAMicrosoft ExcelMicrosoft ApplicationsMicrosoft Office

Last Comment

Subodh Tiwari (Neeraj)

8/22/2022 - Mon

Subodh Tiwari (Neeraj)

12/29/2018

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 Diaz

12/29/2018

ASKER

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)

12/29/2018

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Luis Diaz

12/31/2018

ASKER

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)

12/31/2018

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 Diaz

1/1/2019

ASKER

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Luis Diaz

1/3/2019

ASKER

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)

1/3/2019

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 Diaz

1/3/2019

ASKER

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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Subodh Tiwari (Neeraj)

1/4/2019

Please try the attached...
Is_In_Is_Na-v2.xlsm

Luis Diaz

1/4/2019

ASKER

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)

1/4/2019

That assumes the row1 is the header row and the actual data starts from row2 for both the source and the target ranges.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Luis Diaz

1/4/2019

ASKER

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?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)