Conditional formatting formula to highlight different values in each column

[barruel]

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

[karolus]

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

CF_combined_below_limit_and_row_is_odd.ods (23.8 KB)

[barruel]

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?

[karolus]

@barruel

Is there a chance to do it without any extra rows?

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!

[barruel]

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.