Conditional Formatting II

[Guest]

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

 

[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

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?

 

[Guest]

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

 

[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

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?

 

[Guest]

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

 

[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

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

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

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

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

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

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

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

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

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

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

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

 

[Guest]

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

 

[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