Search
Close this search box.

Excel Colourfunction

Using Format Painter to assign Subtotals

Adding in 3 dimensions by rows, columns and colours. The colour feature is not built-in but required some Visual Basic (VBA) programming code written by and was available from a now unlisted “http://www.ozgrid.com/forum/
showthread.php?t=27182″.

See more information at OzGrid.com

The code has been used to write a template tailored as a monthly report which allows you add numbers to individual activities (or employees) on a daily basis and same time assign the number by a fill colour to – for example – a Department. Each Department has it’s own colour, and a number entered for the employee is assigned to a Department by filling the cell with the colour for that Department.

In our template, you just enter the date of the first day of the month for which you want to make your report. “01/02/2024” sets the form for February 2024 and automatically sets a header showing the weekdays at the top and highlighting weekends with a yellow background colour. It also indicates the number of days in the month, including the 29 days applicable for February in leap years.

The form has a lookup table which makes this automatic for any date from 01 January 2000 to 31 December 2100, covering a span of 101 years.

Ingredients in this file:

The Excel Colourfunction Template includes 4 Tabs:

Instructions, Example, Blank Template and LookUps.
The file contains:

  • Visual Basic code – the code proded by Ozgrid enables the colourfunction.
  • IF functions – used in combination with the VBA Colourfunction to determine the number of entries to sum up for each Department.
  • Lookup – used to determine the number of days applicable in the wanted Month and the name of each week day for every date in the month.
  • Weekday function – used to determine what day of the week applies to the 1.st of the month in the report. The function returns a number (like “1” for Mondays) and a Lookup table translates these numbers to (abbreviated) names: “1” is “Mo”, “2” is “Tu”, etc.
  • Conditional formatting – used to determine which columns are weekends (Sa / Su) and highlight those column cells.
  • Tip – you can use the Excel “Format painter” to pick up the fill colour of the Department to which you want to assign each of your number entries, and then “paste” that colour into your data entry, See the video demo.
Demo on Youtube

Template

Blank Template - Excel Colourfunction
Excel Colourfunction
Format Painter on Excel Menu
Format Painter on Excel Menu

Note: The file is stored as a .zip and will need to be downloaded, unzipped and opened in Excel.