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

 

[jgalas]

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.

I tried it and it works.
however, when cell j15 is no longer red, the 5 bellow cells formatted remain red

 

[awoohaw]

Why did cell J15 become no longer red?
Did the values for the formulas in J16 to J20 change from TRUE to FALSE?

(which of these values in the CF formula changed:
$A81
Calendar!$F$23
$C82
Calendar!$AK$23 ?)

 

[jgalas]

Por que a célula J15 não ficou mais vermelha?

Por que a célula J15 não ficou mais vermelha?
(qual destes valores mudou:
US$ 81
Calendário!$F$23
$C82
Calendário!$AK$23?)

Both can change

$C82 is that it has changed

 

[awoohaw]

Both

Again, what did you do that caused the color of J15 to change from [RED] to something else?
O que você fez para deixar o J15 vermelho?

 

[jgalas]

Novamente, o que você fez para que a cor do J15 mudasse de [VERMELHO] para outra coisa?
O que você fez para deixar o J15 vermelho?

J15 data it's June 10, 2024
I have change the year to 2023 and J15 data it is now June 5, 2023.

Data (days) in range ("D15:AF15") changes depending on the month and year
$C81 contains control number
$A81 table name where to look for data (day of month and year)

 

[awoohaw]

that is the reason why. You have changed the value of cell J15, but none of the components of the conditional formatting.

But, it also does not say how J15 changed color to no fill. Did you do anything else? Did you copy the date from another cell? Did you manually change the format? Please say everything you did to cell J15.

 

[jgalas]

editing.

nothing else was changed

 

[awoohaw]

Then your conditional formatting formula is not the one you have shared.
Where is the YEAR of any of these cells examined:
J15
$A81
Calendar!$F$23
$C82
Calendar!$AK$23

In one of my original posts I asked you to share your workbook to a file sharing service. Please do so.

 

[jgalas]

file sharing

WeTransfer | Send Large Files Fast - Up To 2GB Free

The simple, quick and secure way to send your files around the world without an account. Share your files, photos, and videos today for free.

we.tl

 

[awoohaw]

file sharing

WeTransfer | Send Large Files Fast - Up To 2GB Free

The simple, quick and secure way to send your files around the world without an account. Share your files, photos, and videos today for free.

we.tl

that requires a log in and has unavoidable tracking, so it is definitely not the simplest way to share files.
Can't you use drop box, google docs, or have a microsoft OneDrive folder?