none
Compare two Excel Worksheets and highlight differences in one sheet RRS feed

  • Question

  • Hello all,

     I have two worksheets in the same workbook. The first worksheet contains last periods score matrix, the second worksheet contains this periods score matrix.

     I am struggling to find a away to highlight those cells in this periods worksheet that are different from last periods worksheet.

     I have been able to go as far as identifying the changed cells. I know that works from use of 'MsgBoxes', however I cannot find a way to highlight the identified cells. Its probably because I have chosen to go about this the wrong way entirely. Can someone please give me a guide as to how I should go about this?

     The code I have that works (according to MsgBox's anyway) is below. I would very much appreciate any guidance whatsoever.
     Thanks,

    Option Explicit
    Sub B_HighlightDifferences()
    'Workbooks("Scoring Matrix NEW").Activate
        Dim varScoring As Variant
        Dim varScoring_OLD As Variant
        Dim strRangeToCheck As String
        Dim irow As Long
        Dim icol As Long
        Dim color As CellFormat
        strRangeToCheck = "bl9:bo15"  'smallrange for testing purposes only
        varScoring = Worksheets("Scoring").Range(strRangeToCheck)
        varScoring_OLD = Worksheets("Scoring_OLD").Range(strRangeToCheck)
        For irow = LBound(varScoring, 1) To UBound(varScoring, 1)
            For icol = LBound(varScoring, 2) To UBound(varScoring, 2)
                If varScoring(irow, icol) = varScoring_OLD(irow, icol) Then
                    ' Cells are identical. ' Do nothing.
                    MsgBox "This has not changed"
                Else
                    ' Cells are different. 
    		' Need code here to highlight each cell that is different
                     MsgBox "This has changed"
    End If
                End If
            Next icol
        Next irow
    End Sub
    

    Friday, May 17, 2013 7:47 PM

Answers

  • Try this:

    Sub B_HighlightDifferences()
        'Workbooks("Scoring Matrix NEW").Activate
        Dim varScoring As Variant
        Dim varScoring_OLD As Variant
        Dim strRangeToCheck As String
        Dim irow As Long
        Dim icol As Long
        strRangeToCheck = "bl9:bo15"  'smallrange for testing purposes only
        varScoring = Worksheets("Scoring").Range(strRangeToCheck)
        varScoring_OLD = Worksheets("Scoring_OLD").Range(strRangeToCheck)
        For irow = LBound(varScoring, 1) To UBound(varScoring, 1)
            For icol = LBound(varScoring, 2) To UBound(varScoring, 2)
                If varScoring(irow, icol) = varScoring_OLD(irow, icol) Then
                    ' Cells are identical. ' Do nothing.
                Else
                    ' Cells are different.
                    Worksheets("Scoring").Range(strRangeToCheck).Cells(irow, icol) _
                        .Interior.Color = vbRed
                End If
            Next icol
        Next irow
    End Sub


    Regards, Hans Vogelaar


    Friday, May 17, 2013 8:25 PM

All replies

  • Try this:

    Sub B_HighlightDifferences()
        'Workbooks("Scoring Matrix NEW").Activate
        Dim varScoring As Variant
        Dim varScoring_OLD As Variant
        Dim strRangeToCheck As String
        Dim irow As Long
        Dim icol As Long
        strRangeToCheck = "bl9:bo15"  'smallrange for testing purposes only
        varScoring = Worksheets("Scoring").Range(strRangeToCheck)
        varScoring_OLD = Worksheets("Scoring_OLD").Range(strRangeToCheck)
        For irow = LBound(varScoring, 1) To UBound(varScoring, 1)
            For icol = LBound(varScoring, 2) To UBound(varScoring, 2)
                If varScoring(irow, icol) = varScoring_OLD(irow, icol) Then
                    ' Cells are identical. ' Do nothing.
                Else
                    ' Cells are different.
                    Worksheets("Scoring").Range(strRangeToCheck).Cells(irow, icol) _
                        .Interior.Color = vbRed
                End If
            Next icol
        Next irow
    End Sub


    Regards, Hans Vogelaar


    Friday, May 17, 2013 8:25 PM
  • Maybe the simplest way would be to use conditional formatting on the this scoring periods worksheet with a conditional format formula like:

    =A1<>OldScoringSheet!A1

    Then you wouldn't have to use any code at all.

    • Proposed as answer by LEScott Saturday, May 18, 2013 5:02 AM
    Saturday, May 18, 2013 5:02 AM
  • Thanks LEScott

    Unfortunately Excel does not allow references to other worksheets in conditional formatting formula.

    Saturday, May 18, 2013 7:06 AM
  • Thanks very much Hans. Exactly what I needed!


    Phil Payne

    Saturday, May 18, 2013 7:55 AM
  • Which version of Excel are you running?  I tried it  on 2010 and it worked fine.

    Leo Scott

    Sunday, May 19, 2013 5:25 PM
  • Sorry Leo,

    I should have said - Excel 2003.

    Thanks for trying to help me.


    Phil Payne

    Monday, May 20, 2013 4:55 AM