Colour alternative rows in a worksheet

Excel ‘Tables’ are a good example of clearly laid out and visible data from a database in Excel. However there may be situations when you don’t want to use a table for your data and prefer or need to use a standard worksheet to hold lots of data. Tables will automatically create every other row as no fill / some fill which makes it especially easier to view data as you scroll across the page. It would be a monotonous task if you were to carry this out manually, highlighting every other row and using the fill button. It would take you even longer if it was just a small width of columns that you wanted to fill too.

The VBA example below will automate this task exactly as you require. This particular code assumes headings in the top row, row 1 and that the 2nd row will be no fill, then every other one from 3 downwards will be a light grey. We go to 100,000 rows here but we recommend you use last used row functions or choose the end row where applicable.

We also assume that your data is only in columns A to Z. This can be adjusted to your own requirements too. We also assume the colour you require is Grey. See here for some of the most popular colours in their RGB format that you may want to use instead. Alternatively, you can always determine a colours RGB properties in the Excel Ribbon, fill button > More Colors…

It is important to note if you are an Excel user in England that the US Spelling of color is used in all VBA code. If you were to use the English spelling of ‘Colour’ then you would get an error.

Feel free to contact us using the form or leave a message below and we’ll get back to you with any assistance you might need.