Colour Banding With Conditional Formatting

1 minute read

Use Conditional Formatting from the Format menu to apply a format to the cells. The formula used in the Conditional Formatting rule is based solely on the row number, so the formatting it applies will remain intact as you sort the rows or insert or delete rows.

The formatting techniques described here create colour bands of a fixed number of rows, regardless of the content of the cells on worksheet. When banding is applied, the cells will have alternate bands of colour, as shown below:

By using Conditional Formatting rather than directly styling a range, you can prevent the colours from getting out of order when you sort the range or insert or delete rows.

Colour Banding every second row or column

To apply the colour to every second row or column with the colour in the first cell to be formatted, enter the following formula in the formula bar in the Conditional Formatting dialog.

'## Rows
=MOD(ROW(),1*2)+11 '## Columns
=MOD(COLUMN(),1*2)+1

Leave a Comment