Using conditional formatting to dynamically change cell formats

You can use the Conditional Formatting functionality to dynamically change the format of a cell/cells based on one or more criteria.

I will breakdown the steps required to dynamically fill a date list of cells with a colour based on a date entered by the user.

Column B holds the dates that we need to fill with a colour based on the date entered in cell D3

CF1a

In order to apply the Conditional Formatting we need to ensure the ‘Home’ ribbon is selected and then click on the ‘Conditional Formatting’ icon

CF2

Now you have two options for creating a new rule

If you select ‘Manage Rules’ this will show you all the rules currently in place based on

CF3a

Clicking on ‘New Rule’ on either of the dialog boxes above will take you to the dialog box were you can input the rule.  From here you need to select the option to ‘Use a formula to determine which cells to format’

CF5a

At this point we need to enter the formula that will tell Excel which cells to fill and we have two options for entering the formula

CF6a

We can see that the expanded formula in the image on the left is easier to understand and this gives us the option of using a further IF conditions in the formula (nested IFs) if required.  Both work exactly the same in this example.  The condensed way strips out the TRUE and FALSE elements of the formula and any formats selected are returned on the formula rturning a true state.  The second way to enter is better if you are using simple, one condition formulas.

For the condition element of the formula in the image on the left, we need to make the cells to format (any of the cells in the selected range of B3-B33) a relative reference and the cell containing the date entered by the user (cell D3) an absolute reference.  This is because we ALWAYS need to know the value in D3 but check ALL the cells in the range.  By anchoring cell D3 using the $ symbol before the row reference (3), this ensures that the formula always refers to the specified row.  In the example, the column reference (D) is also anchored but as we are applying the conditional formatting to one column only, the $ before the column reference is not necessary.  When you are entering the formula and have selected the range you can press F4 to toggle between the absolute and relative referencing.

Next we need to select the type of formatting to apply to the cells

CF7a

Click ‘Apply’ in the above image on the right and this will give us a preview of how the formula has been interpreted

CF9a

What we needed to achieve was to fill all cells in range B3-B33 with a green colour IF the date in the range was earlier than the date in cell D3.

You can apply any type of formatting available when you click on the Format button but you don’t have all the options you would have by manually formatting a cell.

There are other options available for formatting cells that are not based on a formula that will display traffic light icons, colour scale the cell, display a data bar, based on ranking within a range, above/below average or whether a unique or duplicate value.

Get more great content from Rousseau Associates

Start your project with us today

Call +44 (0)1757 269461

Discuss your requirements today