Help Again with data comparison

M

Marilyne

Hi everybody!

I got very good help that helped me to solve my initial problem in part.

The programing below works well, but what if the data range goes form G2:Q36?

Many thanks to all of you,

Marilyne

Private Sub Worksheet_Calculate()
Dim Sh As Worksheet
Dim MaxVal As Double
Dim MaxSh As String

For Each Sh In Me.Parent.Worksheets
If Sh.Name <> Me.Name Then
If Sh.Range("G2:Q36").Value > MaxVal Then
MaxVal = Sh.Range("G2:Q36").Value
MaxSh = Sh.Name
End If
End If
Next Sh

Select Case MaxSh
Case "Phase 1"
Me.Range("G2:Q36").Interior.ColorIndex = 6
Case "Phase 2"
Me.Range("G2:Q36").Interior.ColorIndex = 4
Case "Phase 3"
Me.Range("G2:Q36").Interior.ColorIndex = 3
Case "Phase 4"
Me.Range("G2:Q36").Interior.ColorIndex = 8
End Select

End Sub
 
Top