Excel : VBA procedure to compare two columns and highligh the different cells and change the value of the identical cells

compare two columns in excel and return common values
compare two columns in excel for match
compare two columns in excel for differences
compare two columns in excel using vlookup
compare two columns in excel for matches
excel compare two lists and return differences
how to compare two columns in excel and highlight the greater value
excel vba to compare two columns and return a value

VBA procedure to compare two different columns in two different worksheets and highlight the different cells in this column and change the equal cells value to a new sequential number - the data doesn't have to be sorted before using the macro

this code will highlight values not in both lists:

Option Explicit

Sub main()
    Dim report1 As Worksheet, report2 As Worksheet
    Set report1 = Worksheets("Sheet1")
    Set report2 = Worksheets("Sheet2")

    Dim ids1 As Variant, ids2 As Variant
    With report1
        ids1 = Application.Transpose(.Range("B2", .Cells(Rows.Count, "B").End(xlUp)).Value)
    End With
    With report2
        ids2 = Application.Transpose(.Range("D2", .Cells(.Rows.Count, "D").End(xlUp)).Value)
    End With

    ProcessOneReport report1, 2, ids2
    ProcessOneReport report2, 4, ids1
End Sub

Sub ProcessOneReport(report As Worksheet, icol As Long, ids As Variant)
    Dim validIdsRng As Range

    With report
        With .Range(.Cells(1, icol), .Cells(.Rows.Count, icol).End(xlUp))
            .AutoFilter Field:=1, Criteria1:=ids, Operator:=xlFilterValues
            If Application.WorksheetFunction.Subtotal(103, .Cells) < .Count Then
                With .Resize(.Rows.Count - 1).Offset(1)
                    Set validIdsRng = .SpecialCells(xlCellTypeVisible)
                    .AutoFilter
                    validIdsRng.EntireRow.Hidden = True
                    With .SpecialCells(XlCellType.xlCellTypeVisible)
                        .Interior.Color = RGB(156, 0, 6) 'Dark red background
                        .Font.Color = RGB(255, 199, 206) 'Light red font color
                    End With
                    validIdsRng.EntireRow.Hidden = False
                End With
            End If
        End With
        .AutoFilterMode = False
    End With
End Sub

How to Compare Two Columns in Excel (for matches & differences), Hover the cursor on the Highlight Cell Rules option. Click on Duplicate Values. > Compare 2 Columns Through VBA in Microsoft Excel Compare 2 Columns Through VBA in Microsoft Excel In case you are wondering how to compare 2 columns of data having rows in millions & extract the unique values among both the columns then you should read this article.

' data are in column b and column d in sheet 1 and sheet2 respectively.
' data doesn't have to be sorted 
' the student ids which are not available in both columns, will be highlighted and have a cell's color background. 
'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.
 Set Report2 = Excel.Worksheets("Sheet2")
 'lastRow = Report.UsedRange.Rows.Count

lastRow = 29

Application.ScreenUpdating = False

Dim seqNo As Integer, seqNo2 As Integer
seqNo = 0
seqNo2 = 0

For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 2).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
            If InStr(1, Report2.Cells(j, 4).Value, Report.Cells(i, 2).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, 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

'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 Report2.Cells(i, 4).Value <> "" Then
            If InStr(1, Report.Cells(j, 2).Value, Report2.Cells(i, 4).Value, vbTextCompare) > 0 Then
                Report2.Cells(i, 4).Interior.Color = RGB(255, 255, 255) 'White background
                Report2.Cells(i, 4).Font.Color = RGB(0, 0, 0) 'Black font color
                seqNo2 = seqNo2 + 1
                Report2.Cells(i, 4).Value = seqNo2
                Report.Cells(j, 2) = seqNo2
                Exit For
            Else
                Report2.Cells(i, 4).Interior.Color = RGB(156, 0, 6) 'Dark red background
                Report2.Cells(i, 4).Font.Color = RGB(255, 199, 206) 'Light red font color
            End If
        End If
    Next j
Next i

Application.ScreenUpdating = True

End Sub

Compare two columns in Excel – Highlight Matches and Differences, Learn how to compare two columns in Excel to highlight matches and Furthermore, we'll use complex VBA functions to check the similarity between records. and identify which rows contain the same value and which ones are different. Example: Apply IF formula to Compare Cells in the same row. To compare two columns in Excel row-by-row, write a usual IF formula that compares the first two cells. Enter the formula in some other column in the same row, and then copy it down to other cells by dragging the fill handle (a small square in the bottom-right corner of the selected cell).

a better answer to compare two columns (column A and A )in two different worksheets (sheet1 and sheet2) and highlight similar cells with same fill green colour.

Sub Demo()

Dim lastRow1 As Long, lastRow2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Long, j As Long, seqNo As Long

 Set ws1 = Excel.Worksheets("Sheet1")

Set ws2 = Excel.Worksheets("Sheet2")
lastRow1 = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Row
lastRow2 = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Row

' lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row ' lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

' Set compareRange = ws1.Range("A1:A" & lastRow1) ' Set toCompare = ws2.Range("A1:A" & lastRow2)

seqNo = 0
For i = 1 To lastRow2
    For j = 1 To lastRow1
        If ws2.Cells(i, 1).Value <> "" Then
            If ws2.Cells(i, 1).Value = ws1.Cells(j, 1).Value Then
                seqNo = seqNo + 1
                ws2.Cells(i, 1).Value = seqNo
                ws1.Cells(j, 1).Value = seqNo
                ws2.Cells(i, 1).Interior.Color = vbGreen
                ws1.Cells(j, 1).Interior.Color = vbGreen
                Exit For
            Else
                'ws2.Cells(i, 1).Interior.Color = vbWhite
                'ws1.Cells(j, 1).Interior.Color = vbWhite
            End If
        End If
    Next j
Next i

End Sub

How to compare two columns in Excel for matches , To find cells in the same row with different content, simply replace "=" with The first COUNTIF counts how many columns have the same value as in the To compare two columns and Excel and highlight cells in column A that I am a beginner in VBA. i need to compare the values in co11 of row1, row2,  I would like to compare 2 cells' value and see whether they are match or not. I know how to do it on excel but I dont' know how to put it vba code. The value of cell A1 is already in the excel. Manually enter a value in Cell B1. click on a button_click sub to see whether the value on 2 cells are the same or not. You can use the IIF function in VBA.

Compare 2 Columns Through VBA in Microsoft Excel, In case you are wondering how to compare 2 columns of data having rows in Question: There are multiple values which exist in columns A & B. I want a macro to For Each rngCell In Range("A2:A40"); we will check for each cell in “rngCell” VBA function to check criteria range B2:B40 with criteria rngCell is equal to  2) I am indeed a more than a beginner at this and thank you for the advice! I do not have a problem with wanting to learn the good habits, though it has been confusing which ones are which, I have start with this a little over two weeks ago with no background whatsoever and had to find a way two make it work in two weeks to have something to show to the management.

Compare two columns and highlight values in common, A conditional formatting formula highlights values in column B that also The same thing happens in column D, a conditional formatting The COUNTIF function counts how many times value in cell B3 is B3 changes to B4 when Excel moves on to next cell below, however, Put your VBA code here. compare two columns and highlight if they are same Hi, I want to compare two columns in Excel 2010 and if they match than I want them to be highlighted, please guide me how could I do that? please provide me step by step directions.

How to compare two columns and find differences in Excel?, Repeat the same for column B, except use this formula and try another font color. In column C, enter the ff. formula into the first cell and then copy it down. Excel 2010 and just highlight the two columns that have the two sets of values I'm comparing, The compare process can be made with the Excel VBA Worksheet. Here are the steps to do this: Select the entire data set. Click the Home tab. In the Styles group, click on the ‘Conditional Formatting’ option. Hover the cursor on the Highlight Cell Rules option. Click on Duplicate Values. In the Duplicate Values dialog box, make sure ‘Duplicate’ is selected. Specify the formatting.

Comments
  • Hi, Is the below an answer or your attempt so far which should be an edit to the question? And please format it legibly. Select all the code and insert it within code tags (Ctrl + K) . It's also very difficult to relate the above to the below without any sample data and related logic.
  • What about a number not used but within the original series?