Marilyne

It worked OK for me. Here's how I might write it:

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("G10").Value MaxVal Then
MaxVal = Sh.Range("G10").Value
MaxSh = Sh.Name
End If
End If
Next Sh

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

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

"Marilyne" wrote in message
...
Hi everybody!

I will try to explain my problem as clearly as possible.

I have a file with 4 different sheets. Sheet Resume, Phase 1, Phase 2,
Phase 3 and Phase 4. I want to compare data from sheet 1 to 4 (same cells)
ex. 1 (G10) 2 (G10) 3 (G10)... and change the background color on the sheet
Resume (G10 which add value of cells G10 from sheet 1 to 4) depending on the
highest value. If the highest value is on Sheet 1 - Background is Yellow,
if the highest value is on sheet 2 - Background is light blue ... Here's the
formula I am using, but its not working an I don't know why...

Private Sub Worksheet_Calculate()
x = [max('Phase 1:Phase 4'!G10)]
x1 = ['Phase 1'!G10]
x2 = ['Phase 2'!G10]
x3 = ['Phase 3'!G10]
x4 = ['Phase 4'!G10]
y = "{" & x1 & "," & x2 & "," & x3 & "," & x4 & "}"
Select Case Evaluate("Match(" & x & "," & y & ", 0)")
Case 1 'le Max est dans "Phase 1"
[G10].Interior.ColorIndex = 6
Case 2 'le Max est dans "Phase 2"
[G10].Interior.ColorIndex = 4
Case 3 'le Max est dans "Phase 3"
[G10].Interior.ColorIndex = 3
Case 4 'le Max est dans "Phase 4"
[G10].Interior.ColorIndex = 8
End Select
End Sub

thank you very much for your help...

Marilyne