Conditional Formatting II

  • Thread starter Chris from Waterworks
  • Start date
C

Chris from Waterworks

Hello all. I received help from Frank yesterday regarding the above topic, colorizing cells based on different values. Where I am now running into a problem is transferring that same data into another sheet using a function to retrieve the data. More specifically, I have 12 sheets with data, one for each month. The 13th sheet compiles all the monthly data into a yearly calendar format extrapolating the data from the 12 monthly sheets. The problem is that the text (or values) copy over, but the bg color doesn't. Any advice
Thanks, Chris
 
F

Frank Kabel

Hi Chris
you're probably out of luck. Formulas can only return/copy values but
not formats. So you have to apply the same kind of conditional formats
to your summary sheet

--
Regards
Frank Kabel
Frankfurt, Germany

Chris from Waterworks said:
Hello all. I received help from Frank yesterday regarding the above
Click to expand...
topic, colorizing cells based on different values. Where I am now
running into a problem is transferring that same data into another
sheet using a function to retrieve the data. More specifically, I have
12 sheets with data, one for each month. The 13th sheet compiles all
the monthly data into a yearly calendar format extrapolating the data
from the 12 monthly sheets. The problem is that the text (or values)
copy over, but the bg color doesn't. Any advice?
 
C

Chris from Waterworks

Hi Frank, I copied a vba script from you yesterday for colorizing cells with multiples values and it works great. The same script doesn't seem to work on the summary sheet, I believe the script is reading the formula, not the cell content. Is this the case
Chris
 
F

Frank Kabel

Hi
could you post the script you use. Problem could be you're using
formulas on your summary sheet but the macro is using the
worksheet_change event. In this case you may use the
worksheet_calculate event instead

--
Regards
Frank Kabel
Frankfurt, Germany

Chris from Waterworks said:
Hi Frank, I copied a vba script from you yesterday for colorizing
Click to expand...
cells with multiples values and it works great. The same script doesn't
seem to work on the summary sheet, I believe the script is reading the
formula, not the cell content. Is this the case?
 
C

Chris from Waterworks

Hi Frank, here is an abbreviated version
Chri

Private Sub Worksheet_Change(ByVal Target As Range
If Target.Cells.Count > 1 Then Exit Su
If Intersect(Target, Me.Range("A1:Z100")) Is Nothing Then Exit Su
On Error GoTo CleanU
Application.EnableEvents = Fals
With Targe
Select Case .Valu
Case "7001 0.5", "7001 0.75", "7001 1.0", "7001 1.25", "7001 1.5", "7001 1.75", "7001 2.0", "7001 2.25", "7001 2.5", "7001 2.75", "7001 3.0", "7001 3.25", "7001 3.5", "7001 3.75", "7001 4.0", "7001 4.25", "7001 4.5", "7001 4.75", "7001 5.0", "7001 5.25", "7001 5.5", "7001 5.75", "7001 6.0", "7001 6.25", "7001 6.5", "7001 7.0", "7001 7.25", "7001 7.5", "7001 7.75", "7001 8.0": .Interior.ColorIndex = 3
Case "7002 0.5", "7002 0.75", "7002 1.0", "7002 1.25", "7002 1.5", "7002 1.75", "7002 2.0", "7002 2.25", "7002 2.5", "7002 2.75", "7002 3.0", "7002 3.25", "7002 3.5", "7002 3.75", "7002 4.0", "7002 4.25", "7002 4.5", "7002 4.75", "7002 5.0", "7002 5.25", "7002 5.5", "7002 5.75", "7002 6.0", "7002 6.25", "7002 6.5", "7002 7.0", "7002 7.25", "7002 7.5", "7002 7.75", "7002 8.0": .Interior.ColorIndex = 4
Case "7003 0.5", "7003 0.75", "7003 1.0", "7003 1.25", "7003 1.5", "7003 1.75", "7003 2.0", "7003 2.25", "7003 2.5", "7003 2.75", "7003 3.0", "7003 3.25", "7003 3.5", "7003 3.75", "7003 4.0", "7003 4.25", "7003 4.5", "7003 4.75", "7003 5.0", "7003 5.25", "7003 5.5", "7003 5.75", "7003 6.0", "7003 6.25", "7003 6.5", "7003 7.0", "7003 7.25", "7003 7.5", "7003 7.75", "7003 8.0": .Interior.ColorIndex = 3
Case "7004 0.5", "7004 0.75", "7004 1.0", "7004 1.25", "7004 1.5", "7004 1.75", "7004 2.0", "7004 2.25", "7004 2.5", "7004 2.75", "7004 3.0", "7004 3.25", "7004 3.5", "7004 3.75", "7004 4.0", "7004 4.25", "7004 4.5", "7004 4.75", "7004 5.0", "7004 5.25", "7004 5.5", "7004 5.75", "7004 6.0", "7004 6.25", "7004 6.5", "7004 7.0", "7004 7.25", "7004 7.5", "7004 7.75", "7004 8.0": .Interior.ColorIndex = 4
Case "7100 0.5", "7100 0.75", "7100 1.0", "7100 1.25", "7100 1.5", "7100 1.75", "7100 2.0", "7100 2.25", "7100 2.5", "7100 2.75", "7100 3.0", "7100 3.25", "7100 3.5", "7100 3.75", "7100 4.0", "7100 4.25", "7100 4.5", "7100 4.75", "7100 5.0", "7100 5.25", "7100 5.5", "7100 5.75", "7100 6.0", "7100 6.25", "7100 6.5", "7100 7.0", "7100 7.25", "7100 7.5", "7100 7.75", "7100 8.0": .Interior.ColorIndex =
Case "5001 0.5", "5001 0.75", "5001 1.0", "5001 1.25", "5001 1.5", "5001 1.75", "5001 2.0", "5001 2.25", "5001 2.5", "5001 2.75", "5001 3.0", "5001 3.25", "5001 3.5", "5001 3.75", "5001 4.0", "5001 4.25", "5001 4.5", "5001 4.75", "5001 5.0", "5001 5.25", "5001 5.5", "5001 5.75", "5001 6.0", "5001 6.25", "5001 6.5", "5001 7.0", "5001 7.25", "5001 7.5", "5001 7.75", "5001 8.0": .Interior.ColorIndex =
Case "5003 0.5", "5003 0.75", "5003 1.0", "5003 1.25", "5003 1.5", "5003 1.75", "5003 2.0", "5003 2.25", "5003 2.5", "5003 2.75", "5003 3.0", "5003 3.25", "5003 3.5", "5003 3.75", "5003 4.0", "5003 4.25", "5003 4.5", "5003 4.75", "5003 5.0", "5003 5.25", "5003 5.5", "5003 5.75", "5003 6.0", "5003 6.25", "5003 6.5", "5003 7.0", "5003 7.25", "5003 7.5", "5003 7.75", "5003 8.0": .Interior.ColorIndex =
Case "5004 0.5", "5004 0.75", "5004 1.0", "5004 1.25", "5004 1.5", "5004 1.75", "5004 2.0", "5004 2.25", "5004 2.5", "5004 2.75", "5004 3.0", "5004 3.25", "5004 3.5", "5004 3.75", "5004 4.0", "5004 4.25", "5004 4.5", "5004 4.75", "5004 5.0", "5004 5.25", "5004 5.5", "5004 5.75", "5004 6.0", "5004 6.25", "5004 6.5", "5004 7.0", "5004 7.25", "5004 7.5", "5004 7.75", "5004 8.0": .Interior.ColorIndex =
Case "5020 0.5", "5020 0.75", "5020 1.0", "5020 1.25", "5020 1.5", "5020 1.75", "5020 2.0", "5020 2.25", "5020 2.5", "5020 2.75", "5020 3.0", "5020 3.25", "5020 3.5", "5020 3.75", "5020 4.0", "5020 4.25", "5020 4.5", "5020 4.75", "5020 5.0", "5020 5.25", "5020 5.5", "5020 5.75", "5020 6.0", "5020 6.25", "5020 6.5", "5020 7.0", "5020 7.25", "5020 7.5", "5020 7.75", "5020 8.0": .Interior.ColorIndex =
Case "5023 0.5", "5023 0.75", "5023 1.0", "5023 1.25", "5023 1.5", "5023 1.75", "5023 2.0", "5023 2.25", "5023 2.5", "5023 2.75", "5023 3.0", "5023 3.25", "5023 3.5", "5023 3.75", "5023 4.0", "5023 4.25", "5023 4.5", "5023 4.75", "5023 5.0", "5023 5.25", "5023 5.5", "5023 5.75", "5023 6.0", "5023 6.25", "5023 6.5", "5023 7.0", "5023 7.25", "5023 7.5", "5023 7.75", "5023 8.0": .Interior.ColorIndex = 8
Case "5021 0.5", "5021 0.75", "5021 1.0", "5021 1.25", "5021 1.5", "5021 1.75", "5021 2.0", "5021 2.25", "5021 2.5", "5021 2.75", "5021 3.0", "5021 3.25", "5021 3.5", "5021 3.75", "5021 4.0", "5021 4.25", "5021 4.5", "5021 4.75", "5021 5.0", "5021 5.25", "5021 5.5", "5021 5.75", "5021 6.0", "5021 6.25", "5021 6.5", "5021 7.0", "5021 7.25", "5021 7.5", "5021 7.75", "5021 8.0": .Interior.ColorIndex = 8

End Select
End With
CleanUp:
Application.EnableEvents = True

End Sub
 
F

Frank Kabel

Hi
I assume your summary sheet uses formulas. So use the
worksheet_claculate event instead of the worksheet_change event

--
Regards
Frank Kabel
Frankfurt, Germany

Chris from Waterworks said:
Hi Frank, here is an abbreviated version.
Chris


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:Z100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
Select Case .Value
Case "7001 0.5", "7001 0.75", "7001 1.0", "7001
Click to expand...
1.25", "7001 1.5", "7001 1.75", "7001 2.0", "7001 2.25", "7001 2.5",
"7001 2.75", "7001 3.0", "7001 3.25", "7001 3.5", "7001 3.75", "7001
4.0", "7001 4.25", "7001 4.5", "7001 4.75", "7001 5.0", "7001 5.25",
"7001 5.5", "7001 5.75", "7001 6.0", "7001 6.25", "7001 6.5", "7001
7.0", "7001 7.25", "7001 7.5", "7001 7.75", "7001 8.0":
..Interior.ColorIndex = 37
Case "7002 0.5", "7002 0.75", "7002 1.0", "7002
Click to expand...
1.25", "7002 1.5", "7002 1.75", "7002 2.0", "7002 2.25", "7002 2.5",
"7002 2.75", "7002 3.0", "7002 3.25", "7002 3.5", "7002 3.75", "7002
4.0", "7002 4.25", "7002 4.5", "7002 4.75", "7002 5.0", "7002 5.25",
"7002 5.5", "7002 5.75", "7002 6.0", "7002 6.25", "7002 6.5", "7002
7.0", "7002 7.25", "7002 7.5", "7002 7.75", "7002 8.0":
..Interior.ColorIndex = 40
Case "7003 0.5", "7003 0.75", "7003 1.0", "7003
Click to expand...
1.25", "7003 1.5", "7003 1.75", "7003 2.0", "7003 2.25", "7003 2.5",
"7003 2.75", "7003 3.0", "7003 3.25", "7003 3.5", "7003 3.75", "7003
4.0", "7003 4.25", "7003 4.5", "7003 4.75", "7003 5.0", "7003 5.25",
"7003 5.5", "7003 5.75", "7003 6.0", "7003 6.25", "7003 6.5", "7003
7.0", "7003 7.25", "7003 7.5", "7003 7.75", "7003 8.0":
..Interior.ColorIndex = 39
Case "7004 0.5", "7004 0.75", "7004 1.0", "7004
Click to expand...
1.25", "7004 1.5", "7004 1.75", "7004 2.0", "7004 2.25", "7004 2.5",
"7004 2.75", "7004 3.0", "7004 3.25", "7004 3.5", "7004 3.75", "7004
4.0", "7004 4.25", "7004 4.5", "7004 4.75", "7004 5.0", "7004 5.25",
"7004 5.5", "7004 5.75", "7004 6.0", "7004 6.25", "7004 6.5", "7004
7.0", "7004 7.25", "7004 7.5", "7004 7.75", "7004 8.0":
..Interior.ColorIndex = 46
Case "7100 0.5", "7100 0.75", "7100 1.0", "7100
Click to expand...
1.25", "7100 1.5", "7100 1.75", "7100 2.0", "7100 2.25", "7100 2.5",
"7100 2.75", "7100 3.0", "7100 3.25", "7100 3.5", "7100 3.75", "7100
4.0", "7100 4.25", "7100 4.5", "7100 4.75", "7100 5.0", "7100 5.25",
"7100 5.5", "7100 5.75", "7100 6.0", "7100 6.25", "7100 6.5", "7100
7.0", "7100 7.25", "7100 7.5", "7100 7.75", "7100 8.0":
..Interior.ColorIndex = 5
Case "5001 0.5", "5001 0.75", "5001 1.0", "5001
Click to expand...
1.25", "5001 1.5", "5001 1.75", "5001 2.0", "5001 2.25", "5001 2.5",
"5001 2.75", "5001 3.0", "5001 3.25", "5001 3.5", "5001 3.75", "5001
4.0", "5001 4.25", "5001 4.5", "5001 4.75", "5001 5.0", "5001 5.25",
"5001 5.5", "5001 5.75", "5001 6.0", "5001 6.25", "5001 6.5", "5001
7.0", "5001 7.25", "5001 7.5", "5001 7.75", "5001 8.0":
..Interior.ColorIndex = 6
Case "5003 0.5", "5003 0.75", "5003 1.0", "5003
Click to expand...
1.25", "5003 1.5", "5003 1.75", "5003 2.0", "5003 2.25", "5003 2.5",
"5003 2.75", "5003 3.0", "5003 3.25", "5003 3.5", "5003 3.75", "5003
4.0", "5003 4.25", "5003 4.5", "5003 4.75", "5003 5.0", "5003 5.25",
"5003 5.5", "5003 5.75", "5003 6.0", "5003 6.25", "5003 6.5", "5003
7.0", "5003 7.25", "5003 7.5", "5003 7.75", "5003 8.0":
..Interior.ColorIndex = 7
Case "5004 0.5", "5004 0.75", "5004 1.0", "5004
Click to expand...
1.25", "5004 1.5", "5004 1.75", "5004 2.0", "5004 2.25", "5004 2.5",
"5004 2.75", "5004 3.0", "5004 3.25", "5004 3.5", "5004 3.75", "5004
4.0", "5004 4.25", "5004 4.5", "5004 4.75", "5004 5.0", "5004 5.25",
"5004 5.5", "5004 5.75", "5004 6.0", "5004 6.25", "5004 6.5", "5004
7.0", "5004 7.25", "5004 7.5", "5004 7.75", "5004 8.0":
..Interior.ColorIndex = 8
Case "5020 0.5", "5020 0.75", "5020 1.0", "5020
Click to expand...
1.25", "5020 1.5", "5020 1.75", "5020 2.0", "5020 2.25", "5020 2.5",
"5020 2.75", "5020 3.0", "5020 3.25", "5020 3.5", "5020 3.75", "5020
4.0", "5020 4.25", "5020 4.5", "5020 4.75", "5020 5.0", "5020 5.25",
"5020 5.5", "5020 5.75", "5020 6.0", "5020 6.25", "5020 6.5", "5020
7.0", "5020 7.25", "5020 7.5", "5020 7.75", "5020 8.0":
..Interior.ColorIndex = 8
Case "5023 0.5", "5023 0.75", "5023 1.0", "5023
Click to expand...
1.25", "5023 1.5", "5023 1.75", "5023 2.0", "5023 2.25", "5023 2.5",
"5023 2.75", "5023 3.0", "5023 3.25", "5023 3.5", "5023 3.75", "5023
4.0", "5023 4.25", "5023 4.5", "5023 4.75", "5023 5.0", "5023 5.25",
"5023 5.5", "5023 5.75", "5023 6.0", "5023 6.25", "5023 6.5", "5023
7.0", "5023 7.25", "5023 7.5", "5023 7.75", "5023 8.0":
..Interior.ColorIndex = 8
Case "5021 0.5", "5021 0.75", "5021 1.0", "5021
Click to expand...
1.25", "5021 1.5", "5021 1.75", "5021 2.0", "5021 2.25", "5021 2.5",
"5021 2.75", "5021 3.0", "5021 3.25", "5021 3.5", "5021 3.75", "5021
4.0", "5021 4.25", "5021 4.5", "5021 4.75", "5021 5.0", "5021 5.25",
"5021 5.5", "5021 5.75", "5021 6.0", "5021 6.25", "5021 6.5", "5021
7.0", "5021 7.25", "5021 7.5", "5021 7.75", "5021 8.0":
..Interior.ColorIndex = 8
 
C

Chris from Waterworks

Hello Frank. I have changed worksheet_change to worksheet_calculate however I am getting the following error: "a compiler error Procedure declaration does not match description of event or procedure having the same name" I must be missing something here.
Thanks, Chris
 
F

Frank Kabel

Hi
just changing the name is not enough :)
This event procedure has the following syntax:
Private Sub Worksheet_Calculate()

You have to check within this procedure if your values has been changed
and act accordingly
 
Top