# Conditional formatting formula to highlight different values in each column

Hello.

I have a spreadsheet which contains, among other things, a list of the marks my students have got throughout their work. I want to highlight all the cells below certain values, but these values are different in some columns.

Currently I do it with a couple of simple conditional formatting formulas like

``````AND(MOD(ROW();2)=1;A1<5)
``````

and

``````AND(MOD(ROW();2)=0;A1<5)
``````

Which highlight the value and alternate background color in each row for readability on a per column basis.

This works fine, but given Calcâ€™s tendency to fragment the ranges in Conditional formatting when copying, pasting, creating or deleting cells (and even when undoing the changes), it would make my life much easier if I can do the cell highlighting job with a single formula which highlights cells below different values in each column.

I havenâ€™t been lucky searching for it on the internet, so Iâ€™m asking here.: Is such a thing possible or is the 2 formulas per column approach the only way to do it?

TIA

Some guesswork:
CF is hierarchical, first matching Condition winsâ€¦see the CF for Range B2:E30 in the attached Document:

1 Like

Thanks, karolus.

For the record, the document contains a CF set of rules for the range B2:E30.

These rules are 2:

1. Highlight everything below the value set in the first row (absolute reference to the row \$1, but relative to the column, so â€śCell valueâ€ť > â€śis less thanâ€ť > â€śB\$1â€ť).
2. Give a different shade to the odd rows through the formula ISODD(ROW()).

Which, if I understand correctly, can be merged in this single formula:

``````AND(ISODD(ROW());B2>B\$1)
``````

This works nicely if there are fallback styles that match the cell background shade set in condition 2 for odd rows, which I happen to have.

This relies on having the values of every column on a cell which serves only such purpose, which is something I thought of but discarded because I didnâ€™t want an extra row nor a hidden row.

Is there a chance to do it without any extra rows? Iâ€™m thinking not because that would mean limiting with a formula the scope of the conditions, but such scope is hardcoded in the range the conditions apply to.

Am I correct?

this Â»extra rowÂ« gives the convinience to define only one condition[set] for the whole Range, without the need to hardcode each Column seperatly, + if you want to set other limits youâ€™ll just edit this Â»extra_rowÂ« and youâ€™ll done!
of course the Â»extra_rowÂ« must not occur above the Data, it may be somewhere else out of the view or on another sheet!

Thanks. I see it clear now.

Itâ€™s either an extra row, which can be hidden or placed elsewhere, o define the range for each column separately.

Thanks again.