Can a Conditional Formatting Formula Be Stored in a Cell/Cells on the Spreadsheet and Still Work?

[MEUserII]

I would like to know if it's possible to store a conditional formatting formula in a cell/cells on the spreadsheet, and still have it work?

For example, consider the cells:

	Column A	Column B
Row 1	1	A
Row 2	B	2
Row 3	3	C
Row 4	D	4

<tbody>
</tbody>

If the formula:=ISNUMBER(A1) is entered with condition: fill - yellow as a conditional formatting formula where the range in the conditional formatting rule is set to A1:B4 then the effect will be the following fill effect (fill effect denoted in brackets).

	Column A	Column B
Row 1	[YELLOW]	[NO COLOR]
Row 2	[NO COLOR]	[YELLOW]
Row 3	[YELLOW]	[NO COLOR]
Row 4	[NO COLOR]	[YELLOW]

<tbody>
</tbody>

However, this requires that the conditional formatting formula is stored in the Conditional Formatting Rules Manager, so the question is this: is there a way to have this conditional formatting formula stored on the spreadsheet in some cell or cells, and still have the conditional formatting effect (fill yellow) of the conditional formatting formula work for range A1:B4 without being directly stored in the Conditional Formatting Rules Manager?

 

[RoryA]

My first question is why?

 

[Joe4]

The big question is, why do you want to do it this way? In order to use Conditional Formatting, you will need to use Conditional Formatting rules (no way around that).

If you did not want to store the Conditional Formatting Formulas in Conditional Formatting, you could store the formula in cells, and then apply the Conditional Formatting Rules, but that would still be a formula, in an of itself.

For example, let's say that you store the =ISNUMBER(A1) formula to cell C1.
Then you would select cell A1, and enter the following formula for CF:

=C1

The caveat to using this method is:
- you are still, in fact, using CF formulas
- you would need to have a separate formula on you worksheets for every single cell that you wanted to apply Conditional Formatting to (i.e. need to enter a formula for A2 in C2, A3 in C3, etc)

The other alternative to not using Conditional Formatting at all is to use VBA.