Omit zero values from conditional formatting

[stefanaalten]

How can I use conditional formatting to apply a colour scale to shade highest value brightly and scale the colour down to lowest non-zero value in a muted colour, but cells with zero values (many) not to have any shading. Data consists of positive integers in a column.

Other conditional formatting rules apply shading based on row number, so:
- If cell value is 0, conditional formatting that applies shading based on row number should determine the cell shading colour.
- If cell value is non-zero, apply shading to that cell with the colour based on the value (high=bright, down to low but non-zero = muted, effectively "overriding" shading based on row number).

(Changing the zero values to blanks ("") is not an option as this means that the column sorting does not work the way it is supposed to: the sort becomes alphabetical instead of numerical and if I then choose "Z to A" this results in all the "" values being listed first, whereas they should come last since the "" represents 0!)

Many thanks in advance for any help with this!