Excel VBA: compara columns row by row

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,
I am looking for a procedure to cover the following requirements and add it to my personal.xlsb
1-Inputbox: “Enter & select the header range which is going to receive the comparison”
2-Inputbox: “Enter column letter for first comparison column”
3-Inputbox: “Enter column letter for second comparison column”
4-Perform a comparison row by row between first column and second column and reported TRUE when it is equal FASE when it is FALSE. For FALSE values report fill the various range with red colour
5-For header value related to 1- perform concatenation between header of first comparison column header & “vs” second comparison column header

I attached dummy file.

If you have questions, please contact me
Compare-Columns.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sam JacobsDirector of Technology Development, IPM

Commented:
Try this (also attached):
Option Explicit

Sub CompareColumns()

Dim strCol As String
Dim targetCol As Integer
Dim col1 As Integer
Dim col2 As Integer
Dim offset As Integer
Dim idx As Double
Dim lastrow As Double

strCol = InputBox("Please enter the column to contain the comparison results:")
If strCol = "" Then End
targetCol = Range(strCol & 1).Column
strCol = InputBox("Please enter the first column to compare:")
If strCol = "" Then End
col1 = Range(strCol & 1).Column
strCol = InputBox("Please enter the second column to compare:")
If strCol = "" Then End
col2 = Range(strCol & 1).Column

Cells(1, targetCol) = Cells(1, col1) & "vs" & Cells(1, col2)
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For idx = 2 To lastrow
    If Cells(idx, col1) = Cells(idx, col2) Then
        Cells(idx, targetCol) = "TRUE"
    Else
        Cells(idx, targetCol) = "FALSE"
        Cells(idx, targetCol).Interior.ColorIndex = 3
    End If
Next

End Sub

Open in new window

Compare-Columns.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you for this proposal, I tested and the procedure cover almost the various requirement still pending to highlight in red values in first column comparison and second column comparison as performed in result column.
Could you please help me to adjust it?Compare-columns.png
Sam JacobsDirector of Technology Development, IPM

Commented:
I'm sorry ... I don't understand what is not working correctly.
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:
When column D is equal to FALSE, values related to column A and B should be also highlighted in red as shown in my example:
Thank you in advance for your help.
Compare-columns-2.png
Sam JacobsDirector of Technology Development, IPM

Commented:
Got it! Give me a few minutes to update...
Director of Technology Development, IPM
Commented:
Updated version attached.
Compare-Columns.xlsm
Luis DiazIT consultant

Author

Commented:
Thank you, unable to test right now. I will keep you informed.
Sam JacobsDirector of Technology Development, IPM

Commented:
No problem ... thanks for the update.
Luis DiazIT consultant

Author

Commented:
Tested and it works! Thank you again for your help!
Sam JacobsDirector of Technology Development, IPM

Commented:
You are most welcome.

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