Conditional formatting embedded within formula

[sharshra]

I have extensively used conditional formatting for various purposes. But these are made on a set of cells having some values. It is like hard coding the range of cells & applying conditional formatting on it.

Instead, I´m curious to know if it is possible to embed conditional formatting within excel formula. When the formula executes, it will automatically apply conditional formatting to the range of cells where results are updated. Thinking of something similar to dynamic array SPILL the values. Along with SPILL of values, is it possible to SPILL the conditional formatting also?

Consider the following example. I'm using a simple formula to SUM. On top of the formula output, conditional formatting is applied to highlight in red the values less than 10. While the formula SPILL the result, can I embed conditional formatting also within the formula so that both the result & conditional formatting will SPILL?

excel problems.xlsx
	B	C
56	1
57	4	8
58	7
59		9
60	11	2
61		76
62		98
63	34	3
merge tables

excel problems.xlsx
	G
56	1
57	12
58	7
59	9
60	13
61	76
62	98
63	37
merge tables

Cell Formulas
Range	Formula
G56:G63	G56	=BYROW(B56:C63,LAMBDA(r,SUM(r)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G56:G63	Expression	=$G56<10	text	NO

 

[dreid1011]

No, not through traditional use of conditional formatting. You will either need to apply the formatting/conditional formatting with some VBA or set a large enough area for a normal CF rule that would cover any range the formula will spill to.

 

[sharshra]

Thanks, @dried1011. I´m already setting large range for CF, but wanted to check if there is a way to SPILL CF.

 

[dreid1011]

You're welcome.