Conditional formatting using macros in Excel
Helen Bradley explains how to apply multiple conditional formats to a single range in Excel using VBA.
When you use conditional formats to highlight information in a worksheet, you can only use one set of formats at a time. If you want your user to be able to switch between formats, you’ll need to build a solution using VBA. This month, we look at a way to define multiple conditional formats for a single range of cells and apply them as and when required using worksheet buttons.
OUR CONDITIONAL FORMATS
We’ll use four formats — one to identify entries that are weekend days, one that highlights higher-than-average values and one that highlights alternate rows. The final one will allow us to highlight information relating to a manager chosen from a dropdown list we’ve placed in cell F3. Each conditional format is based on a formula, the data range is A3:C33 and these are the formulae: =WEEKDAY($A3,2)>=6 =$B3>AVERAGE($B$3:$B$33) =mod(row(),2)=0 =$C3=$F$3
THE MACRO SOLUTION
In the macro solution, each format will be accessed via a CommandButton, which is added to the sheet via the Developer tab. This is the code for each of the four buttons — the code appears in the Sheet code area:
Private Sub CommandButton1_ Click() Dim formatCode As Integer formatCode = 1 applyFormat (formatCode) End Sub
Private Sub CommandButton2_ Click() Dim formatCode As Integer formatCode = 2 applyFormat (formatCode) End Sub
Private Sub CommandButton3_ Click() Dim formatCode As Integer formatCode = 3 applyFormat (formatCode) End Sub Private Sub CommandButton4_ Click() Dim formatCode As Integer formatCode = 4 applyFormat (formatCode) End Sub
Each button calls a routine called applyFormat which handles applying the Conditional format depending on the value of a variable, formatCode, sent to that routine. In this way, one procedure can handle all the formatting. This is the procedure applyFormat — the code appears in workbook module:
Sub applyFormat(code As Integer)
Dim userRange As Range, userFormat As FormatCondition
Set userRange = Range(“=$a$3:$c$33”)
Range(“f:f”).EntireColumn. Hidden = True
If userRange. FormatConditions.Count <> 0 Then
userRange.FormatConditions. Delete End If Select Case code Case Is = 1
Set userFormat = userRange.FormatConditions. Add(xlExpression, xlFormula, “=$B3>AVERAGE($B$3:$B$33)”)
userFormat.Interior. ThemeColor = xlThemeColorAccent2 Case Is = 2
Set userFormat = userRange.FormatConditions. Add(xlExpression, xlFormula, “=WEEKDAY($A3,2)>=6”)
userFormat.Interior. ThemeColor = xlThemeColorAccent3 Case Is = 3
Set userFormat = userRange.FormatConditions. Add(xlExpression, xlFormula, “=mod(row(),2)=0”)
userFormat.Interior. ThemeColor = xlThemeColorAccent4 Case Is = 4
Range(“f:f”). EntireColumn.Hidden = False
Set userFormat = userRange.FormatConditions. Add(xlExpression, xlFormula, “=$C3=$F$3”)
userFormat.Interior. ThemeColor = xlThemeColorAccent5 End Select
userFormat.Interior. TintAndShade = 0.8
End Sub
The macro removes any format already in place and it hides column F. The appropriate conditional format is then added and a theme colour specified for the cells. If the fourth button is selected, column F is made visible to give the user access to the dropdown list in that column.