Assume the following numbers in range C5:C22. Heading is in cell C4.
Range C5:C7 – 11
Range C8:C11 – 14
Range C12:C13 – 23
Range C14-C19 – 56
Range C20-C22 – 78
One may want to colour cells for every change in number in range C5:C22 with the following two modifications:
1. Shading should be for alternate band of rows i.e. C5:C7 should be coloured, C8:C11 should not be coloured, C12:C13 should be coloured, C14:C19 should not be coloured and C20:C22 should be coloured.
2. Shading should vary with the range being filtered i.e. If range C5:C22 is filtered to exclude 14 and 56, then numbers 11 and 78 should be coloured i.e. range C5:C7 and C20:C22 should be coloured.
To accomplish this, try the following:
a. Select cell C5 and go to Home > Format > Conditional formatting > New Rule > Use a formula to determine which cells to format
b. In the formula box there, enter the following formula
c. Click on Format and select any Fill colour
d. Click on OK > OK > Apply
e. Copy cell C5, select range C6:C22 and right-click > Paste Special > Formats
With no filter in place, colouring will happen as mentioned in point 1 above. Auto filter the range now on some values and the colour banding should change.