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 =
Case 2 'le Max est dans "Phase 2
[G10].Interior.ColorIndex =
Case 3 'le Max est dans "Phase 3
[G10].Interior.ColorIndex =
Case 4 'le Max est dans "Phase 4
[G10].Interior.ColorIndex =
End Selec
End Su

thank you very much for your help..

Marilyne

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

Thank you very, very much for your help

What if the data range is G2:Q36

Marilyn

----- Dick Kusleika wrote: ----

Marilyn

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

Dim Sh As Workshee
Dim MaxVal As Doubl
Dim MaxSh As Strin

For Each Sh In Me.Parent.Worksheet
If Sh.Name < Me.Name The
If Sh.Range("G10").Value MaxVal The
MaxVal = Sh.Range("G10").Valu
MaxSh = Sh.Nam
End I
End I
Next S

Select Case MaxS
Case "Phase 1
Me.Range("G10").Interior.ColorIndex =
Case "Phase 2
Me.Range("G10").Interior.ColorIndex =
Case "Phase 3
Me.Range("G10").Interior.ColorIndex =
Case "Phase 4
Me.Range("G10").Interior.ColorIndex =
End Selec

--
Dick Kusleik
MVP - Exce
www.dicks-clicks.co
Post all replies to the newsgroup

"Marilyne" wrote in messag
..
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 shee
Resume (G10 which add value of cells G10 from sheet 1 to 4) depending on th
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 th
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 =
Case 2 'le Max est dans "Phase 2
[G10].Interior.ColorIndex =
Case 3 'le Max est dans "Phase 3
[G10].Interior.ColorIndex =
Case 4 'le Max est dans "Phase 4
[G10].Interior.ColorIndex =
End Selec
End Su
thank you very much for your help..
Marilyn

Marilyne

Try this

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 < Me.Name 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

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

"Marilyne" wrote in message
...
Thank you very, very much for your help.

What if the data range is G2:Q36?

Marilyne

----- Dick Kusleika wrote: -----

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