Data Comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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,
Click to expand...
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
Click to expand...
 
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 said:
Hi everybody
Click to expand...
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(
Click to expand...
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
Click to expand...
Click to expand...
 
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 said:
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 said:
Hi everybody!
Click to expand...
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()
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Marilyne

What is the exact error message that you are getting? Is it a compile error
or a run-time error?

Is there any chance that the cells contain text and not numbers?

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

Marilyne said:
I'm confused... It doesn't work...

I copied the exact formula but it stop compiling data there, saying that there is incompatibility...

MaxVal = Sh.Range(cell.Address).Value
MaxSh = Sh.Name

I have very small programing knowledges and my jobs depend on that work...
Click to expand...
So please could you help me again.
 
Execution Error '13

Type incompatibilit

It occurs every time I compile the data

There is no text in the cell - but there is accounting data and when there is no value in the cell there is - $.
 
I think I know why I have problem when compliling the data

I have much more than 5 worksheets but I want this little program to apply only o
Report - Phase 1 - Phase 2 - Phase 3 - Phase

The other 5 worksheets which contain general data should not be part of it. I think that I must specify on which worksheet the programing apply. If so, could you indicate me how

Many, Many thank

Marilyne
 
Aha! Good catch Marilyne.

Change this line

If Sh.Name <> Me.Name Then

to

If Sh.Name = "Report" Or Sh.Name = "Phase 1" Or Sh.Name = "Phase 2" Or
Sh.Name = "Phase 3" Or Sh.Name = "Phase 4" Then

Or, if you only have four sheets that begin with "Phase", you could shorten
it to this

If Sh.Name = "Report" Or Left(Sh.Name,5) = "Phase" Then

Hopefully that will solve it. If not, be sure to let me know.

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

Marilyne said:
I think I know why I have problem when compliling the data.

I have much more than 5 worksheets but I want this little program to apply only on
Report - Phase 1 - Phase 2 - Phase 3 - Phase 4

The other 5 worksheets which contain general data should not be part of
Click to expand...
it. I think that I must specify on which worksheet the programing apply.
If so, could you indicate me how.
 
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 Workshee
Dim MaxVal As Doubl
Dim MaxSh As Strin
Dim cell As Rang

For Each cell In Me.Range("G2:Q36").Cell
For Each Sh In Me.Parent.Worksheet
If Sh.Name = "Report" Or Left(Sh.Name, 5) = "Phase" The
If Sh.Range(cell.Address).Value > MaxVal The
MaxVal = Sh.Range(cell.Address).Valu
MaxSh = Sh.Nam
End I
End I
Next S

Select Case MaxS
Case "Phase 1
cell.Interior.ColorIndex =
Case "Phase 2
cell.Interior.ColorIndex =
Case "Phase 3
cell.Interior.ColorIndex =
Case "Phase 4
cell.Interior.ColorIndex =
End Selec
Next cel

End Su

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

Marilyne
 
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 said:
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
Click to expand...
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.
 
This is what I thought about adding but I don't know if it is correct. And I<m not sure where exactly I should put i

If MaxVal = 0 The
cell.Interior.ColorIndex =
End If
 
Yahoo!!! I got it... I put these lines of cod

If MaxVal = 0 The
cell.Interior.ColorIndex =
End I

Just before

MaxVal =

It works..

Do you think that I did it correctly... I'm quite proud of myself!!! I kind of star to understand how this whole programming thing works.

THANK YOU!!!!!

Marilyne
 
I deleted some worksheet that I was not using anymore and everything stopped. The program do compile correctly but when I do a step by step compiling it jumps over

If Sh.Range(cell.Address).Value > MaxVal The
MaxVal = Sh.Range(cell.Address).Valu
MaxSh = Sh.Nam

This if is FALSE but I don't understand why

:-

Thanks

Marilyne
 
Just so you'll know... This is the whole programing

Private Sub Worksheet_Calculate(

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

For Each cell In Me.Range("G2:Q36").Cell
For Each Sh In Me.Parent.Worksheet
If Sh.Name = "Rapport" Or Left(Sh.Name, 5) = "Phase" The
If Sh.Range(cell.Address).Value > MaxVal The
MaxVal = Sh.Range(cell.Address).Valu
MaxSh = Sh.Nam
End I
End I
Next S

Select Case MaxS
Case "Phase 1
cell.Interior.ColorIndex =
Case "Phase 2
cell.Interior.ColorIndex =
Case "Phase 3
cell.Interior.ColorIndex =
End Selec
If MaxVal = 0 The
cell.Interior.ColorIndex =
End I
MaxVal = 0
Next cel

End Su

What is the meaning of that line: If Sh.Range(cell.Address).Value > MaxVal Then
We did not set any value to MaxVal... I really don't understand how it works tha
 
Marilyne

Here's some notes so you have a better understanding:

'This event sub fires whenever the "Rapport" (I think - see note below)
worksheet calculates
Private Sub Worksheet_Calculate()
Click to expand...

'Set up variables
Dim Sh As Worksheet
Dim MaxVal As Double
Dim MaxSh As String
Dim cell As Range
Click to expand...

'Loop through every cell in the range G2:Q36. These cells contain
'a formula like "=Phase1:Phase4!G2" The Me keyword refers to the sheet
'that contains the code, so in this case Me = Sheets("Rapport")
For Each cell In Me.Range("G2:Q36").Cells
Click to expand...

'Loop through each worksheet in the workbook. The Parent of the worksheet
'is the workbook, so Me.Parent is a reference to the workbook.
For Each Sh In Me.Parent.Worksheets
Click to expand...

'Test the sheet name. If the name = Rapport or starts with Phase, then
continue
'processing, otherwise skip to the corresponding End If. See the note below
'regarding the sheet named Rapport and the sheet named Resume.
If Sh.Name = "Rapport" Or Left(Sh.Name, 5) = "Phase" Then
Click to expand...

'Here we are recording the largest value found on the sheets tested.
MaxValue is set
'to zero (implicitly) when the macro starts. Cell.Address returns, for G2
as an example $G$2.
'so Sh.Range(cell.Address).Value returns the value of G2 on the worksheet
Sh.
If Sh.Range(cell.Address).Value > MaxVal Then
Click to expand...

'If the largest value is found, store it and the sheet name on which it was
found
MaxVal = Sh.Range(cell.Address).Value
MaxSh = Sh.Name
End If
End If
Next Sh
Click to expand...

'Once the sheet with the largest value is found, color the cell based on
'the sheet's name. Your addition is fine (because it works) but I would
'write it a little differently as noted below.
Select Case MaxSh
Case "Phase 1"
cell.Interior.ColorIndex = 6
Case "Phase 2"
cell.Interior.ColorIndex = 4
Case "Phase 3"
cell.Interior.ColorIndex = 3
End Select
If MaxVal = 0 Then
cell.Interior.ColorIndex = 8
End If
MaxVal = 0
Next cell

End Sub
Click to expand...

I had the understanding that you had a sheet named Resume and 4 sheets named
Phase 1, 2, 3, 4. And that the Resume sheet had formulas like
=Phase1:Phase4!G2. If I misunderstood and the sheet with the formulas is
called Rapport, then you should eliminate that from the If statement above.
You only want to test the sheets that have cells in the formula, not the
sheet with the actual formula. So that If statement would then be

If Left(Sh.Name,5) = "Phase" Then

and you would eliminate the Or part that tests for a sheet named Rapport. I
hope that's clear.

I would write that later section like this

If MaxValue = 0 Then
cell.Interior.ColorIndex = 0
Else
Select Case MaxSh
Case "Phase 1"
cell.Interior.ColorIndex = 6
etc...
End Select
End If

Your way isn't wrong, it's just a little more inefficient than this way.

Now that you (hopefully) have a better understanding of what's going wrong,
maybe we can narrow down the problem. Also, feel free to email a copy of
the workbook to me so that we're looking at the same thing.
 
Back
Top