Conditional formatting (with formula) on non-empty cell

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.

2 answers

  • Votes
  • Date
  • Descending date
  1. 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
    1. It works too, thank you!
    2. Hello Via55
      Thank you for the relative reference formula

      Sayanel01: the $ in front of the column and/or row is precisely used to fix the reference