Conditional formatting, format from a cell?

[Jaymond Flurrie]

Can I without using VBA or macros or other code based solutions do a conditional formatting, where the result (format) is picked from a cell? Like "if true, use format of the cell A1, if false, use the format from cell A2"?

 

[saboh12617]

Hello,

Yes, but depending on how cells A1 and A2 are conditionally formatted (if they are) it can be tricky. You will have do so some logic but basically what you want is to do this :

IF(condition, Format_A1, Format_A2)

So you need to translate Format_A1 and Format_A2 to their respective formulas, and instead of IF, use a logic AND with two formats like so

AND(condition, condition_format_a1) => format_a1
AND(NOT(condition), condition_format_a2) => format_a2

If format in A1 and A2 are static, then it is easy and the above principle simplifies to

Condition => format_a1
NOT(condition) = > format_a2

 

[Jaymond Flurrie]

Hello,

Yes, but depending on how cells A1 and A2 are conditionally formatted (if they are) it can be tricky. You will have do so some logic but basically what you want is to do this :

IF(condition, Format_A1, Format_A2)

So you need to translate Format_A1 and Format_A2 to their respective formulas, and instead of IF, use a logic AND with two formats like so

AND(condition, condition_format_a1) => format_a1
AND(NOT(condition), condition_format_a2) => format_a2

If format in A1 and A2 are static, then it is easy and the above principle simplifies to

Condition => format_a1
NOT(condition) = > format_a2

Sounds promising, but I didn't completely understand this, for example, I have the "New Formatting Rule"-window, where I can set the "Format..." but that only gives me Number, Font, Border and Fill options, which all are just static, none of them is "like cell A1". Where is this Condition => format_a1 (or rather, format_like_a1_is_formatted) input?

The format is simple, it can be something as simple as interior (cell fill) color. Like, say, my A1 is red and A2 is yellow and based on A3 (let's say, that matches to either text A1 or then it doesn't, in which case we'll assume that A2 is wanted). But it's not static, that's the whole point here. If A1 is made Blue, the cells where I want to apply this should also be Blue and if A1 is Green, the target cells also needs to change to be Green.

 

[saboh12617]

Hello,

Then it gets very complicated, because for each color change in A1 due to CF (if you do it manually there is no way to keep track of it) you will need to add a new CF to the cell to be coloured like A1, with the same condition as A1.

In other words, you can not tell a cell "copy this other cell color". You can give it the same formula as the CF of the target cell, and therefore copy its behaviour, but if there are many conditions, for each of them you will have to repeat the procedure.

Moreover, since you also want to add cell A3 in the mix, then you have to edit all CF to take into account this other value.

… It is very fastidious. VBA is better for this job. 5 lines and you're good to go.

Remember Excel works with values, not colours. They are just eyes sugar.

 

[Jaymond Flurrie]

Hello,

Then it gets very complicated, because for each color change in A1 due to CF (if you do it manually there is no way to keep track of it) you will need to add a new CF to the cell to be coloured like A1, with the same condition as A1.

In other words, you can not tell a cell "copy this other cell color". You can give it the same formula as the CF of the target cell, and therefore copy its behaviour, but if there are many conditions, for each of them you will have to repeat the procedure.

Moreover, since you also want to add cell A3 in the mix, then you have to edit all CF to take into account this other value.

… It is very fastidious. VBA is better for this job. 5 lines and you're good to go.

Remember Excel works with values, not colours. They are just eyes sugar.

Yeah, like said, with VBA that's trivial. I have a hunch that XLSM is not even allowed to be used as a file format (due to security etc reasons).