VBA Excel: Compare columns based on a config Sheet

Hello experts,

I use the following code to compare two columns highlight values in green if they match else highlight values in red:


Sub RunCompareCols()
    Call CompareCols("Sheet1", ColLetterToNum("B"), ColLetterToNum("C"), ColLetterToNum("D"))
    Call CompareCols("Sheet3", ColLetterToNum("D"), ColLetterToNum("E"), ColLetterToNum("F"))
End Sub



'####################################################Functions and Procedures####################################################
'################################################################################################################################

Function ColLetterToNum(ByVal sColLetter As String) As Long
' Convert column letter to numeric

    ColLetterToNum = ActiveWorkbook.Worksheets(1).Columns(sColLetter).Column
End Function

Function ColNumToLetter(lColNum As Long) As String
' Convert numeric to column letter

    ColNumToLetter = Split(Cells(1, lColNum).Address, "$")(1)
End Function


Sub CompareCols(wsName As String, Col1Number As Integer, Col2Number As Integer, ColResult As Integer)

    Dim Report As Worksheet
    Dim i As Integer, j As Integer
    Dim lastRow As Integer

    Set Report = Excel.Worksheets(wsName)

    lastRow = Report.UsedRange.Rows.Count

    Application.ScreenUpdating = False
    
    Report.UsedRange.ClearFormats
    

    '-->Loop till the last Row and update the various conditions
    For i = 2 To lastRow
        For j = 2 To lastRow
            'If Report.Cells(i, 1).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
                If InStr(1, Report.Cells(j, Col2Number).Value, Report.Cells(i, Col1Number).Value, vbTextCompare) > 0 Or _
                InStr(1, Report.Cells(j, Col2Number).Value, Report.Cells(i, Col1Number).Value, vbTextCompare) > 0 Then
                    Report.Cells(i, Col1Number).Interior.Color = RGB(0, 255, 0) 'Green background
                    Report.Cells(i, Col1Number).Font.Color = RGB(0, 0, 0) 'Black font color
                    Report.Cells(i, Col2Number).Interior.Color = RGB(0, 255, 0) 'Green background
                    Report.Cells(i, Col2Number).Font.Color = RGB(0, 0, 0) 'Black font color
                    Report.Cells(i, ColResult).Value = "TRUE"
                    Exit For
                Else
                    Report.Cells(i, Col1Number).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, Col1Number).Font.Color = RGB(255, 199, 206) 'Light red font color
                    Report.Cells(i, Col2Number).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, Col2Number).Font.Color = RGB(255, 199, 206) 'Light red font color
                    Report.Cells(i, ColResult).Value = "FALSE"
                End If
            'End If
        Next j
    Next i

Application.ScreenUpdating = True

End Sub

Open in new window


 I would like to:


1-Perform this action based on a Config Sheet as did for the following question
http://www.experts-exchange.com/questions/28720600/VBA-Excel-Perform-index-match-dynamically-based-on-a-config-Sheet-v2.html

2015-11-12-17_12_27-Microsoft-Excel-.png(Keeping the same checking control)


2-Report the count of False and True n°

3-Modify the instr condition in order to have an exact comparison. In fact when I compare blank values with a filled value it return True and I don't know how should I built this condition in order to return FALSE.

I place the code used for the dynamic Index match based on a Config sheet in the reference module. It can help.

Thank you very much for your help.
Dynamic-True-False-2.xlsm
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.

Ejgil HedegaardCommented:
I don't understand the logic of the compare.
It is not a row by row compare, but a compare for values in first column to be part of any value in second column.
Values in second column are not compared to be part of first column.
So what does the colour in the second column mean?

If I change B2 on Sheet1 from 78 to 76, the result change from False to True, because 76 can be found in C13.
And both cells in row 2 will be green.
Is that intended?

A blank cell in first column will match to be part of any string in second column, and therefore return true.

The 2 Or parts in the if statement for the compare are identical
If InStr(1, Report.Cells(j, Col2Number).Value, Report.Cells(i, Col1Number).Value, vbTextCompare) > 0 Or _
InStr(1, Report.Cells(j, Col2Number).Value, Report.Cells(i, Col1Number).Value, vbTextCompare) > 0 Then

Open in new window

Both search first column (i) in second column (j).
Is that a typing error, and should the second instr be reversed to search second value in first column?

Changing and adding check for not blank cells, it will be
If (InStr(1, Report.Cells(j, Col2Number).Value, Report.Cells(i, Col1Number).Value, vbTextCompare) > 0 Or _
InStr(1, Report.Cells(i, Col1Number).Value, Report.Cells(j, Col2Number).Value, vbTextCompare) > 0) _
And Report.Cells(i, Col1Number).Value <> "" And Report.Cells(j, Col2Number).Value <> "" Then

Open in new window

Then B17 value 896 will be true, since 89 in C2 is part of 896.
Is that correct?

Please explain the compare logic.
0
LD16Author Commented:
Thank you very much for your comment.

In fact the idea is to compare row by row an not to find the value which is in the difference columns.
Based on existing column (Field1) I would perform an index match to bring Field3 with the other macro and the idea is to identify when info of Field1 is different from Field2 but row by row.

Here is my first proposal, let me know if we can optimize something:
Still pending: Update the True and False values.
Revise the CheckConfig
And understand why I have a True when I have an empty value and not empty value example Sheet1 row21
Dynamic-True-False-3.xlsm
0
Ejgil HedegaardCommented:
The used function Instr search for Field1 to be part of Field2.
An empty value is a string with no length, and is part of any string, hence True in row 21.
identify when info of Field1 is different from Field2
, means compare the entire content of the 2 cells, so Instr can not be used.

Here is a row by row match, for the entire content in the 2 cells.
The conversion function UCase (upper case) is used because in VBA strings compare, A is not equal to a.
Result is False if one of the cells are blank.
With count for True/False and revised CheckConfig.
Dynamic-True-False-3-1.xlsm
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LD16Author Commented:
Okey, I was able to solve all the requirements:

I have simplify all the code and I was able to cover my final requirements.

Let me know if you see some improvement optimization areas in the code.
Dynamic-True-False-4.xlsm
0
LD16Author Commented:
Sorry, I haven't see your comment before posting my comment.
0
LD16Author Commented:
With your code you have replied to my imporvements / optimization areas question. By calling directly the function CompareCols it is much easier.

Thank you again for your help and for your great support!
0
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
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.