Excel: Conditional Formatting and a Half

Have you ever wondered whether you can have more than 3 colours with conditional formatting. I don’t know about Excel 2007 but certainly in Excel 2003 the limit of 3 conditionals can at times be too restrictive. So is there any way of getting more than 3 colours? Yes! Continuing on the theme of the last 2 posts we can use the same technique as with the formulae insertion and Sean’s OLE codes to conditionally format any number of conditions.

So where to start – first we need to handle the work sheet’s On Change event handler as detailed in this post.

So if you’ve followed the instruction in the previous post you should have code that looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Next we need to think about how to restrict the formatting to a particular column or set of rows. If you don’t do this then that formatting will be applied to ANY cell in the worksheet. NOTE: Because we are working with the code page related to the worksheet, this conditional formatting will only work with on this worksheet!

So lets assume we will have our formatting in column 3 (C) and the first row will be reserved for a header, i.e. the formatting will only start at rows 2. At the moment there’s no code to write because we need to know how the formatting should related to the other information in the worksheet. For this example lets assume that the first column (A) contains along the letters from A to Z with a different colour for each and that the second column (B) contains integer numbers such that our formatting will be bold for even numbers and italic for old. Not really a real world example but should be sufficient to illustrate the point.

So now we need to do one type of formatting for changes in column A and another for changes in column B. Here’s the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim vCell As Range
  Dim i As Long
  For Each vCell In Target
    If vCell.Row > 1 Then
      Select Case vCell.Column
        ' Colour Formatting
        Case 1
          Select Case Cells(vCell.Row, 1).Value
            Case "A": Cells(vCell.Row, 3).Interior.ColorIndex = 36
            Case "B": Cells(vCell.Row, 3).Interior.ColorIndex = 3
            Case "C": Cells(vCell.Row, 3).Interior.ColorIndex = 2
            Case "D": Cells(vCell.Row, 3).Interior.ColorIndex = 4
          Case Else
            Cells(vCell.Row, 3).Interior.ColorIndex = xlNone
          End Select
        ' Font Formatting
        Case 2
          If IsNumeric(Cells(vCell.Row, 2).Value) Then
            i = CLng(Cells(vCell.Row, 2).Value)
            Cells(vCell.Row, 3).Font.Bold = (i Mod 2 = 0)
            Cells(vCell.Row, 3).Font.Italic = Not (i Mod 2 = 0)
          Else
            Cells(vCell.Row, 3).Font.Bold = False
            Cells(vCell.Row, 3).Font.Italic = False
          End If
      End Select
    End If
  Next vCell
End Sub

I’ve only implemented the first four letters of the alphabeta but I think you can get the idea. I’ve used the ColorIndex property to set the colours – this relates to the colour palette in Excel. To set colours by another mechanism you can use Color = RBG(#red,#green,#blue).

Here’s a screen shot of the output:

Here’s the Excel workbook.