CODEWITHSUNDEEP
excelvba
user2395242
user2395242

Reputation: 1

compare contents of two excel worksheets

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

Upvotes: 0

Views: 188

Answers (1)

Floris
Floris

Reputation: 46375

You did most of the hard work. I would change the following. Add:

dim newCell as Range
Application.ScreenUpdating = False

... then inside your for loop:

Set newCell = varScoring.Cells(irow, icol)

Then you should be able to apply any formatting you want to newCell (which is a Range object) when you find it's different.

newCell.Select
With Selection.Interior
    .Color = 49407
    ' any formatting you want.
End With

At the end of your routine, turn screen updating on again:

Application.ScreenUpdating = True

Let me know if this makes sense.

Upvotes: 1

Related Questions