Compare two worksheets and highlight changed cells on one worksheet

[Phil Payne]

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 mush 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
         [I]       ' Cells are identical. ' Do nothing.[/I]
                MsgBox "This has not changed"
            Else
               [I] ' Cells are different. [/I]
		[B]' Need code here to highlight each cell that is different[/B]
                 MsgBox "This has changed"


End If
            End If
        Next icol
    Next irow

End Sub

 

[BrianMH]

Option Explicit


Sub B_HighlightDifferences()
    'Workbooks("Scoring Matrix NEW").Activate
    Dim rScoring As Range
    Dim rScoring_OLD As Range
    Dim strRangeToCheck As String
    Dim irow As Long
    Dim icol As Long
    Dim color As CellFormat
    Dim rCell As Range
    
    strRangeToCheck = "bl9:bo15"  'smallrange for testing purposes only
    
    rScoring = Worksheets("Scoring").Range(strRangeToCheck)
    rScoring_OLD = Worksheets("Scoring_OLD").Range(strRangeToCheck)
    
    For irow = 1 To rScoring.Rows.Count
        For icol = 1 To rScoring.Columns.Count
            
            If rScoring(irow, icol) = rScoring_OLD(irow, icol) Then
                'do nothing
            Else
                ' Cells are different.
                ' Need code here to highlight each cell that is different
                Set rCell = rScoring(irow, icol)
                rCell.Interior.color = vbRed
                
            End If
        End If
    Next icol
Next irow


End Sub

This should work. I've changed your variants to ranges for ease of coding (it enables intellisense).

 

[Phil Payne]

Hello BrianMH,

Thanks very much for your prompt reply.

I had an 'endIf without block if' msg so I removed the last endif, but on the next run I got a runtime error (91) "Object variable or With block variable not set" on the code line -
" rScoring = Worksheets("Scoring").Range(strRangeToCheck)"

Any ideas?

Option Explicit


Sub B_HighlightDifferences()
    'Workbooks("Scoring Matrix NEW").Activate
    Dim rScoring As Range
    Dim rScoring_OLD As Range
    Dim strRangeToCheck As String
    Dim irow As Long
    Dim icol As Long
    Dim color As CellFormat
    Dim rCell As Range
    
    strRangeToCheck = "bl9:bo15"  'smallrange for testing purposes only
    
    rScoring = Worksheets("Scoring").Range(strRangeToCheck)
    rScoring_OLD = Worksheets("Scoring_OLD").Range(strRangeToCheck)
    
    For irow = 1 To rScoring.Rows.Count
        For icol = 1 To rScoring.Columns.Count
            
            If rScoring(irow, icol) = rScoring_OLD(irow, icol) Then
                'do nothing
            Else
                ' Cells are different.
                ' Need code here to highlight each cell that is different
                Set rCell = rScoring(irow, icol)
                rCell.Interior.color = vbRed
                
            End If
        End If
    Next icol
Next irow


End Sub

This should work. I've changed your variants to ranges for ease of coding (it enables intellisense).

 

[Phil Payne]

I should have mentioned i am using Excel 2003!

 

[Phil Payne]

Solution found - see below


Thanks all.

     If varScoring(irow, icol) = varScoring_OLD(irow, icol) Then
                ' Cells are identical. ' Do nothing.
            Else
                ' Cells are different.
[I][B]                Worksheets("Scoring").Range(strRangeToCheck).Cells(irow, icol).Interior.Color = vbRed[/B][/I]
            End If