# How to override conditional formatting or set precedence?

I want to have alternating background colors for even/odd rows (better for the eyes) but also allow the user to override the background color if desired. AFAIK, there's two ways--create an Auto format or use conditional formatting.

For my case, conditional formatting is preferable because Auto format carries over the formatting that was set on the row initially (e.g. creating a duplicate row right underneath an existing row would yield both rows having the same background color), whereas conditional formatting is dynamic where it formats the cells automatically.

However, it seems that conditional formatting has higher precedence than manually changing (direct formatting?) the background color of a cell that has been formatted conditionally, resulting in no changes to the cell unless the conditional formatting rule is removed.

edit retag close merge delete

Afaik the only way to override an existing conditional formatting is by an additional condition which evaluates to truebefore the condition causing the alternating background colorization.

( 2020-03-20 13:32:08 +0200 )edit

@Opaque Do you have an example for a noobie? Is it possible to check whether a cell has a different background color that is not "no-fill" first before applying the rule to color the cell if it is on the even row if the background color is "no-fill"?

Although the current rule I'm using is ISODD(ROW()) and ISEVEN(ROW()), which acts on the entire row (don't know if there's a way to check if a cell is on an odd or even row which would make more sense for the example above).

( 2020-03-20 18:16:45 +0200 )edit

ISODD(ROW()) and ISEVEN(ROW())do act on each cell and not on entire rows (if you limit the Range to e.g A1:G25 only the cells within that range are using alternating background colors, while all other keep their default appearance).

Define a new rule (e.g. you want to background numbers with another color) and put that before the ISVEN() and ISODD() rules according to the following screenshot (just to illustrate, what my first comment tries to express)

If the first condition is met, this one applies and Conditional Formatting is done and ISEVEN() or ISODD() do not get applied.

( 2020-03-21 13:58:52 +0200 )edit