How to override conditional formatting or set precedence?

[zf]

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.

[anon73440385]

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

[zf]

@anon73440385 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).

[anon73440385]

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.

[Hrbrgr]

@anon73440385, please write an answer, thanks.

[zf]

@anon73440385 You are able to set precedence for conditional formatting rules that apply to the same cells, but what about to rules that apply to different sets of cells that may potentially have some overlapping sells? For example, a rule for alternating row colors that apply to all cells and then a rule for background colors for some cells in a particular column. On the window for managing conditional formats, there isn’t a way to set precedence for the rules the way you can for conditions that act on the same set of cells.

Can you confirm that this is the case?