Conditional formatting: Colour scales with additional criteria

[njimack]

I don't think this is possible, but you never know.
In the sample data below, I would like to apply colour scales to A,B,C and D, ignoring the sub-total and Total. I know I could apply CF to all 6 rows and then apply a 2nd rule that forces the sub-total and Total row to be displayed as white. However, this would distort the shading of the 4 rows in question because the 4 and 10 would still be used to shade the values for A,B,C and D.

I'd prefer a formula approach but I'm open to VBA/UDF.


[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Sub-Total
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

Figured it out, thanks to this post