Filling backcolor cells color based on the color of a cell

[jgalas]

I want, based on the color of one cell (red), to fill the 5 cells below (pink). As picture.

The cell background (red) is defined by CF.
=IF($A81="calendar";IF(MONTH(Calendar!$F$23)=$C82;DAY(Calendar!$F$23);-1);IF($A81="calendar_bi";IF(MONTH( Calendar!$AK$23)=$C82;DAY(Calendar!$AK$23);-1)))

It will be applied to several rows.

Thank you in advance for any help

 

[awoohaw]

Set CF rules for the cells underneath using the same formula as used for the primary cell.

Not your exact example. (Sorry, I'm not going to recreate your scenario from scratch. You can share a copy of your workbook to a file share site, if you like).

Book1
	A	B	C	D	E	F	G
1
2	Column						Developing CF Rule
3	ABCD			12	2	3	TRUE
4	dfadf						TRUE
5	fgf						TRUE
6	dsfg						TRUE
7	hggf						TRUE
8
Sheet10

Cell Formulas
Range	Formula
G3:G7	G3	=SUM($D$3:$F$3)=17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A7	Expression	=SUM($D$3:$F$3)=17	text	NO
A3	Expression	=SUM($D$3:$F$3)=17	text	NO

 

[jgalas]

Set CF rules for the cells underneath using the same formula as used for the primary cell.

Not your exact example. (Sorry, I'm not going to recreate your scenario from scratch. You can share a copy of your workbook to a file share site, if you like).

Book1
	A	B	C	D	E	F	G
1
2	Column						Developing CF Rule
3	ABCD			12	2	3	TRUE
4	dfadf						TRUE
5	fgf						TRUE
6	dsfg						TRUE
7	hggf						TRUE
8
Sheet10

Cell Formulas
Range	Formula
G3:G7	G3	=SUM($D$3:$F$3)=17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A7	Expression	=SUM($D$3:$F$3)=17	text	NO
A3	Expression	=SUM($D$3:$F$3)=17	text	NO

awoohaw, thank you for your answer but it doesn't work that way.
It fills all cells with the background color defined in CF.

What I need is a way to detect the background color of a cell (red) and fill the 5 below with the same color.

 

[awoohaw]

I'm not sure what you mean compared to your initial question. I used blue instead of pink, my error there.
As I said. in my original post. The rule you need to use is the the same rule, but with different relative references.

what worksheet and cell is the red "10" in?

 

[awoohaw]

You cannot use the cell color for Conditional Formatting.
(There are posts somewhere where people can do it with VBA - but just using the same rule to apply a different color to different cells will work!)

Please take a look at how Minda Treacy approaches Conditional Formatting:

 

[jgalas]

I'm not sure what you mean compared to your initial question. I used blue instead of pink, my error there.
As I said. in my original post. The rule you need to use is the the same rule, but with different relative references.

what worksheet and cell is the red "10" in?

hello
I appreciate your response.
The problem isn't with the color you used
Maybe I didn't express myself well.

I have range ("D9:AF9"), range ("D15:AF15"), ... in which some cells are filled with red automatically by CF.
What I want is to detect the cell filled with red (it can be any cell and they can change) and fill red (or pink, ...) in the next 5 below them.

I've been working on it and got this code:
Sub DetectCorFill()
Dim rng As Range
Dim cell As Range

Set rng = Union(Range("D9:AF9"), Range("D15:AF15"))

For Each cell In rng
If cell.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
Range(cell.Offset(1, 0), cell.Offset(5, 0)).Interior.Color = RGB(255, 0, 0)
End If
Next cell
End Sub

It works!

However, when the cells filled with red change into range ("D9:AF9"), range ("D15:AF15"),..... the 5 bellow cells formatted remain.

Any tips?
Thank you for your help.

 

[awoohaw]

Your VBA skills are better than mine.

Whenever I use conditional formatting based on how a particular cell is formatted I use the same rule.
It is that simple. You have some complexities in your CF formula, but that should make no difference.
Assume cell X999 is formatted because Z49 + Y9000 + Q42 = 1000.... the you should be able to put that
CF rule in ANY cell's CF that you want.

while giving me your range references was helpful... it does not answer my specific question of what cell is the red 10 in?

 

[jgalas]

Your VBA skills are better than mine.

Whenever I use conditional formatting based on how a particular cell is formatted I use the same rule.
It is that simple. You have some complexities in your CF formula, but that should make no difference.
Assume cell X999 is formatted because Z49 + Y9000 + Q42 = 1000.... the you should be able to put that
CF rule in ANY cell's CF that you want.

while giving me your range references was helpful... it does not answer my specific question of what cell is the red 10 in?

The red 10 is in J15

 

[jgalas]

J15 backcolor is red with CF when:
=SE($A15="calendario";SE(MÊS(Calendario!$F$16)=$C16;DIA(Calendario!$F$16);-1);SE($A15="calendario_bi";SE(MÊS(Calendario!$AK$16)=$C16;DIA(Calendario!$AK$16);-1)))
is TRUE

 

[awoohaw]

thanks can you do me a favor as a proof of concept.
Put the CF formula for cell J15, in cells J16:J20? do not copy and paste.
IF cell J15 is still [RED], if not go one in the row that is.
Go to your CF rule editor for cell J15, and copy it the rule formula.
Then select J16:J20 and press F2, paste the CF formula in, be sure it includes the "=" sign. Then press CNTL-ENTER.
If you get all TRUES, then you have a CF rule that works. Just enter a new rule for those cells, paste the formula and select a fill color.

So... Based upon your formula above.
go to cell J16,
click conditional formatting, new rule based on formula
paste this in the formula bar:

=SE($A15="calendario";SE(MÊS(Calendario!$F$16)=$C16;DIA(Calendario!$F$16);-1);SE($A15="calendario_bi";SE(MÊS(Calendario!$AK$16)=$C16;DIA(Calendario!$AK$16);-1)))

(Yes, it is the same formula)
Select the FILL
Click OK until you get out.