Excel 2003 Conditional Formatting

Hello Experts; I need to apply conditional formatting with color for each cell in Sheet1 whose value is compared with same cell in Sheet2., as per attached file.
A solution is needed for Excel 2003 and earlier versions, where other conditional formatting exists leaving no room for more formulas, so clearly I am looking for a private sub solution to be operating in Sheet1.

Thank you, N2V Conditional-Formatting.xls
Who is Participating?
This? You can add more colours if required.
Sub x()

Dim r As Range

With Sheets("A")
    For Each r In .Range("C3:R22")
        Select Case r.Value - Sheets("B").Range(r.Address).Value
            Case 0: r.Interior.ColorIndex = 10
            Case 1: r.Interior.ColorIndex = 6
            Case 2: r.Interior.ColorIndex = 44
            Case 3: r.Interior.ColorIndex = 3
            Case Else: r.Interior.ColorIndex = 2
        End Select
    Next r
End With
End Sub

Open in new window

Patrick MatthewsCommented:

Assuming you have a range of 6 cells, named Colors, that have the background colors you want to apply for the absolute differences of 0 through 6, this code seems to work for me:

Private Sub Worksheet_Calculate()
    Dim rng As Range
    Dim cel As Range
    Dim OtherCel As Range
    Dim Diff As Long
    Set rng = Range("Grid")
    For Each cel In rng.Cells
        Set OtherCel = Me.Parent.Worksheets("B").Range(cel.Address)
        Diff = Abs(cel - OtherCel)
        cel.Interior.ColorIndex = Range("Colors").Cells(Diff + 1).Interior.ColorIndex
End Sub

Open in new window

Here's a wild guess, don't know if this is what you want:
NewToVBAAuthor Commented:
Both solutions are effective and provide education I was looking for. Thank you very much!

Cheers, V
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.