Marilyne

Sorry about that. We need to set MaxVal back to zero for every cell.
Here's the line you need to add (shown in context)

Select Case MaxSh
Case "Phase 1"
cell.Interior.ColorIndex = 6
Case "Phase 2"
cell.Interior.ColorIndex = 4
Case "Phase 3"
cell.Interior.ColorIndex = 3
Case "Phase 4"
cell.Interior.ColorIndex = 8
End Select
MaxVal = 0 '****New line here
Next cell


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Marilyne" wrote in message
...
I'm getting excited.... It works but with a little bug I think.

In fact, the cell colors spread over all the cells even if the total of
the cells equal 0. Also, if there is data in a cell let say G2 and nothing
in all other cells (0 value) the whole line 2 get colored with the
background color of cell G2.

Here is the formula I'm using:

Private Sub Worksheet_Calculate()

Dim Sh As Worksheet
Dim MaxVal As Double
Dim MaxSh As String
Dim cell As Range

For Each cell In Me.Range("G2:Q36").Cells
For Each Sh In Me.Parent.Worksheets
If Sh.Name = "Report" Or Left(Sh.Name, 5) = "Phase" Then
If Sh.Range(cell.Address).Value MaxVal Then
MaxVal = Sh.Range(cell.Address).Value
MaxSh = Sh.Name
End If
End If
Next Sh

Select Case MaxSh
Case "Phase 1"
cell.Interior.ColorIndex = 6
Case "Phase 2"
cell.Interior.ColorIndex = 4
Case "Phase 3"
cell.Interior.ColorIndex = 3
Case "Phase 4"
cell.Interior.ColorIndex = 8
End Select
Next cell

End Sub

I'm so grateful for your help! Many, many thanks

Marilyne