It's Christmas time again, the season of cheer. You can bring a little festive merriness to your PC by simulating Christmas advent candles using the magic of random numbers combined with conditional formatting.
Why not have a go yourself? I will show you how to develop two versions in just a few steps each. Alternatively you can download the finished version here: Christmas candles v3.
Basic version
Explanation: All four candles are always on.
Step 1 - Create your candles
Make columns narrow, then add background colours for the base, the candles and the wicks. Then add a message at the bottom plus festive icons and shapes (using the Insert ribbon), if you wish.
Step 2 - Add formulas
Enter these formulas in cells that should show flames:
=RANDBETWEEN(50, 100) for lighter flame colours
=RANDBETWEEN(1, 50) for darker flame colours in the centre, above the wick
These formulas generate random numbers every time a calculation is performed. We will use these numbers to set the colours of the flames.
Step 3 – Add conditional formats
Select a block of cells containing the “flame cells” and add conditional formatting as follows.
Step 4 – Tidy up
For a cleaner look…
- Select then hide unused rows and columns (right click, hide)
- Remove the gridlines (View ribbon, Show section, deselect Gridlines box)
- Protect sheet (Review ribbon) and deselect all options to hide the cell selection
Step 5 - Animate
Press F9 to change the colours. Or press and hold F9 to animate.
Advent Sunday version
Explanation: Each candle only lights up when the related advent Sunday is reached.
Step 1 – Create or copy the basic version
If necessary, unprotect the sheet (Review ribbon) and select and unhide all columns.
Step 2 – Add dates
- Today – enter formula =TODAY()
- Advent Sundays - input the four dates
Step 3 – Amend flame formulas
The formulas must now test if the relevant advent Sunday date has been reached. So each flame tests a different advent date, for example:
=IF($AR$10>=$AR$11; RANDBETWEEN(50;100); 0) for lighter flame colours in the first flame
If the relevant date has not been reached, the cell value is now set to zero.
Step 4 – Amend conditional formats
Add a second conditional format (tip: duplicate the first rule and amend it).
If the cell value is zero, format the colour to match the background so that no flame is visible.
Tidy up and animate
Same as the basic version.
Burning down candles version
In the the download file you will also find another version in which the candles gradually burn down!
I created this based upon (a copy of) the advent Sunday version and used the OFFSET function to simulate the burning-down based upon the number of days each candle has been burning and an input of how many days each row of candle picture lasts (e.g. 4 days), i.e. the "standard candle" offset by a number of rows.
Enjoy!
