Conditional formatting (with formula) on non-empty cell
Sayanel01
Posted messages
82
Status
Member
-
f894009 Posted messages 17417 Registration date Status Member Last intervention -
f894009 Posted messages 17417 Registration date Status Member Last intervention -
Hello,
I have a table that includes values every other row only. (No, I can't delete the empty cells)
I would like to highlight in red the values less than 10, but not color the empty cells.
How can I do that?
Set a rule based on a formula, for example
=AND(IF($A$1:$A$7<10,TRUE),NOT(ISBLANK($A$1:$A$7)))
doesn't work
=AND(IF($A$1<10,TRUE),NOT(ISBLANK($A$1)))
works, but I need to do it for each cell, which defeats the purpose.
Thanks
P.S.: Actually, I would like to know how, in conditional formatting formulas, I can reference "this cell" (---> that is, the value of the cell that will be formatted). It would be very useful in many situations.
I have a table that includes values every other row only. (No, I can't delete the empty cells)
I would like to highlight in red the values less than 10, but not color the empty cells.
How can I do that?
Set a rule based on a formula, for example
=AND(IF($A$1:$A$7<10,TRUE),NOT(ISBLANK($A$1:$A$7)))
doesn't work
=AND(IF($A$1<10,TRUE),NOT(ISBLANK($A$1)))
works, but I need to do it for each cell, which defeats the purpose.
Thanks
P.S.: Actually, I would like to know how, in conditional formatting formulas, I can reference "this cell" (---> that is, the value of the cell that will be formatted). It would be very useful in many situations.
2 answers
-
Hello
For the conditional formatting formula to adapt, a relative reference (and not an absolute one) based on the first cell of the range is needed, so with A1 as the first cell of the range, after selecting the entire range to which the conditional formatting will apply, the formula will be (simplifying your formula):
=AND(A1<10;A1<>"")
or since the cells to be processed are placed one row apart, without worrying about the empty ones, assuming that the filled cells are in the odd rows:
=AND(A1<10;ISODD(ROW())
Best regards
Via
--
"Imagination is more important than knowledge." A. Einstein
