VBA macro to compare two columns and color highlight cell differences

Question

I wanted to color highlight cells that are different from each other; in this case colA and colB. This function works for what I need, but looks repetitive, ugly, and inefficient. I'm not well versed in VBA coding; Is there a more elegant way of writing this function?

EDIT What I'm trying to get this function to do is: 1. highlight cells in ColA that are different or not in ColB 2. highlight cells in ColB that are different or not in ColA

    Sub compare_cols()

    Dim myRng As Range
    Dim lastCell As Long

    'Get the last row
    Dim lastRow As Integer
    lastRow = ActiveSheet.UsedRange.Rows.Count

    'Debug.Print "Last Row is " & lastRow

    Dim c As Range
    Dim d As Range

    Application.ScreenUpdating = False

    For Each c In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
        For Each d In Worksheets("Sheet1").Range("B2:B" & lastRow).Cells
            c.Interior.Color = vbRed
            If (InStr(1, d, c, 1) > 0) Then
                c.Interior.Color = vbWhite
                Exit For
            End If
        Next
    Next

    For Each c In Worksheets("Sheet1").Range("B2:B" & lastRow).Cells
        For Each d In Worksheets("Sheet1").Range("A2:A" & lastRow).Cells
            c.Interior.Color = vbRed
            If (InStr(1, d, c, 1) > 0) Then
                c.Interior.Color = vbWhite
                Exit For
            End If
        Next
    Next

Application.ScreenUpdating = True

End Sub

how about getting rid of VBA completely and just use XL's robust `Conditional Formatting` feature? Also, perhaps this better suited for [Code Review](http://codereview.stackexchange.com/) — Scott Holtzman, Jan 07 '13 at 21:47
@ScottHoltzman Is that feature available across all versions? — Kermit, Jan 07 '13 at 21:49
@njk -> good question. It is, but the features in 07/10 are more robust than 03. I am not sure the differences in 07/10, though, off the top of my head. — Scott Holtzman, Jan 07 '13 at 21:51
@ScottHoltzman I didnt know about the Code Review site. I will post there in the future, apologies. Also, I've just noticed a bug in my code that it skips cells. I suspect its hitting the Exit For and bypassing both For loops instead of just the inner one. — Jose Leon, Jan 07 '13 at 22:09
Yes, your `Exit For` will exit the original `For`. However, it's not really clear what you are trying to highlight to be different, as you loop through each cell in Column B for each cell in Column A, then do the same for the opposite direction, so your colors could change many times in the process, depending on your values. Can you edit your post with some more non-code description of exactly what you are trying to do. — Scott Holtzman, Jan 07 '13 at 22:13
Are you trying to compare cells that are next to eachother? e.g., compare A1 to B1?' — Ross Brasseaux, Jan 08 '13 at 00:14
@njk 07/10 allow formula references off sheet, which is not allowed in 03. Also 07/10 have icon sets whic aren't available in 03. In either case excel will tell you if you save a .xls file with those features used. — Pynner, Jan 08 '13 at 03:30
Range name workarounds can always be used in xl03 for the off-sheet issues — brettdj, Jan 08 '13 at 09:10
@Lopsided I'm trying to ask the question "is A1 in B column?" Kind of like vlookup but by coloring the cell. — Jose Leon, Jan 11 '13 at 03:56
@ScottHoltzman No, sir. The Exit For will only exit the current loop. He had that part right. I would say the most likely culprit is his InStr statement using the numerical value of vbTextCompare (i.e., the fourth parameter). — Ross Brasseaux, Jan 11 '13 at 15:25
@Lopsided -> thanks for the correction on my misinformation. — Scott Holtzman, Jan 11 '13 at 15:32
@user706837 And FYI, you'd probably get a lot more responses if you accepted more answers as "correct". — Ross Brasseaux, Jan 11 '13 at 16:22
@user706837 You're welcome. No worries I was just speaking in general. — Ross Brasseaux, Jan 12 '13 at 05:01

score 4 · Accepted Answer · answered Jan 11 '13 at 15:22

Ah yeah that's cake I do it all day long. Actually your code looks pretty much like the way I'd do it. Although, I opt to use looping through integers as opposed to using the "For Each" method. The only potential problems I can see with your code is that ActiveSheet may not always be "Sheet1", and also InStr has been known to give some issues regarding the vbTextCompare parameter. Using the given code, I would change it to the following:

Sub compare_cols()

    'Get the last row
    Dim Report As Worksheet
    Dim i As Integer, j As Integer
    Dim lastRow As Integer

    Set Report = Excel.Worksheets("Sheet1") 'You could also use Excel.ActiveSheet _
                                            if you always want this to run on the current sheet.

    lastRow = Report.UsedRange.Rows.Count

    Application.ScreenUpdating = False

    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, 2).Value, Report.Cells(i, 1).Value, vbTextCompare) > 0 Then
                    'You may notice in the above instr statement, I have used vbTextCompare instead of its numerical value, _
                    I find this much more reliable.
                    Report.Cells(i, 1).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 1).Font.Color = RGB(0, 0, 0) 'Black font color
                    Exit For
                Else
                    Report.Cells(i, 1).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 1).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
        Next j
    Next i

    'Now I use the same code for the second column, and just switch the column numbers.
    For i = 2 To lastRow
        For j = 2 To lastRow
            If Report.Cells(i, 2).Value <> "" Then
                If InStr(1, Report.Cells(j, 1).Value, Report.Cells(i, 2).Value, vbTextCompare) > 0 Then
                    Report.Cells(i, 2).Interior.Color = RGB(255, 255, 255) 'White background
                    Report.Cells(i, 2).Font.Color = RGB(0, 0, 0) 'Black font color
                    Exit For
                Else
                    Report.Cells(i, 2).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 2).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
        Next j
    Next i

Application.ScreenUpdating = True

End Sub

Things I did differently:

I used my integer method described above (as opposed to the 'for each' method).
I defined the worksheet as an object variable.
I used vbTextCompare instead of its numerical value in the InStr function.
I added an if statement to omit blank cells. Tip: Even if only one column in the sheet is extra long (e.g., cell D5000 was accidentally formatted), then the usedrange for all columns is considered 5000.
I used rgb codes for the colors (it's just easier for me since I have a cheat sheet pinned to the wall next to me in this cubicle haha).

Well that about sums it up. Good luck with your project!

score 1 · Answer 2 · answered Nov 21 '18 at 09:52

'Compare the two columns and highlight the difference

    Sub CompareandHighlight()



        Dim n As Integer
        Dim valE As Double
        Dim valI As Double
        Dim i As Integer

        n = Worksheets("Indices").Range("E:E").Cells.SpecialCells(xlCellTypeConstants).Count
        Application.ScreenUpdating = False

        For i = 2 To n
        valE = Worksheets("Indices").Range("E" & i).Value
        valI = Worksheets("Indices").Range("I" & i).Value

            If valE = valI Then

            Else:

               Worksheets("Indices").Range("E" & i).Font.Color = RGB(255, 0, 0)

            End If
        Next i


    End Sub

' I hope this helps you

VBA macro to compare two columns and color highlight cell differences

2 Answers2

Linked