This article applies to all versions of Microsoft Excel For Windows®, unless stated otherwise.

Author(s): Bob Phillips
© Copyright, 2005, Bob Phillips

Date Written: 16th September 2005
Last Updated: 16th September 2005

Article Summary:

This paper discusses conditional formatting in Excel, how to test whether a cell, or a range of cells, is conditionally formatted, and whether or not one of the conditions is satisfied. Techniques are presented that show.

how to test for a cell being conditionally formatted
whether any of the conditions are met,
the value of a particular attribute of a met condition,
how to process a range of conditionally formatted cells,
a simple conditional formatting colour counting function.

Article Contents:

This paper is structured in the following way:

What Is Conditional Formatting?
Is A Cell Conditionally Formatted
Is A Conditional Format Condition Satisfied?
Testing Specific Conditions
Testing A Multi-cell Range
A Conditionall Formatted Colour Counting Function
Further Enhancements
Acknowledgements and References

This article assumes familiarity with the following:

The Excel Application
Visual Basic For Applications (VBA) programming concepts (for the section Names In VBA)

What Is Conditional Formatting?

Conditional formatting is a feature within Excel that enables a spreadsheet developer to apply a format to a cell depending upon whether a particular condition is satisfied or not.

The formatting that is applied when a conditional formatting condition applies to the cell in addition to any formatting applied directly to the cell. In other words, a cell can be given a particular colour, and it can be given another colour depending upon a condition. In this instance, if the condition is satisfied, the conditional formatting colour will show, if not, the cell colour shows.

Conditional formatting has two types

Cell value, and
Formula.

Within cell value, there are eight possible operators

Equal
Not equal
Greater than
Greater than or equal to
Less than
Less than or equal to
Between
Not between.

Is A Cell Conditionally Formatted?

Determining whether a cell is conditionally formatted, or more precisely whether a cell is conditionally formatted and one of the conditions within that cell conditional formatting is met, is not simple. The usual advice is to developers wanting to determine this is to replicate those conditional tests within an IF test or within VBA. This is good, pragmatic advice, but how do you cope if you have many different types of conditional formatting, or if you inherit a spreadsheet where you have no idea which cells are conditionally formatted, or what the conditions within a conditional format are?

The following function demonstrates how to test whether a cell has conditional formatting applied to it

'---------------------------------------------------------------------
Public Function IsCF(rng As Range)  As Boolean
'---------------------------------------------------------------------
    Set rng = rng(1, 1)
    IsCF = rng.FormatConditions.Count > 0
End Function

Fig. 1.

This is invoked in the following manner

=IsCF(A1) or =IsCF(A1:a2)

This is fine insofar as it goes in that it returns True or False indicating whether conditional formatting has been applied to the cell, or the first cell in a range. What it does not do is indicate whether any of the conditions within conditional formatting have been satisfied or not.

Is A Conditional Formatting Condition Satisfied?

Determining whether a cell has conditional formatting, and whether any of the conditions applied to that conditionally formatted cell is satisfied or not, is surprisingly difficult. The difficulty mainly lies in the number of factors that have to be tested to determine whether a condition is met or not. Both of the condition types, Cell Value Is and Formula Is have to be tested, and all of the operators mentioned in the previous section for Cell Value Is.

Conditional formatting can handle three conditions, and each of these has to be tested to determine whether the condition is met or not. The following function, IsCFMet1 as shown in Figure 2, demonstrates how to test all three conditions assuming a condition type of Cell Value Is, and the operators. The function returns True whether any condition is met, or False if not.

'---------------------------------------------------------------------
Public Function IsCFMet1(rng As Range) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  IsCFMet1 = rng.Value = oFC.Formula1
               Case xlNotEqual
                  IsCFMet1 = rng.Value <> oFC.Formula1
               Case xlGreater
                  IsCFMet1 = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  IsCFMet1 = rng.Value >= oFC.Formula1
               Case xlLess
                  IsCFMet1 = rng.Value < oFC.Formula1
               Case xlLessEqual
                  IsCFMet1 = rng.Value <= oFC.Formula1
               Case xlBetween
                  IsCFMet1 = (rng.Value >= oFC.Formula1 And _
                              rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  IsCFMet1 = (rng.Value < oFC.Formula1 Or _
                              rng.Value > oFC.Formula2)
            End Select
         End If
         If IsCFMet1 Then Exit Function
      Next oFC
   End If

End Function

Fig. 2

As can be seen, the function works in the following way:

the range is restricted to the first cell in the range
the range is tested as to whether it has conditional formatting
each condition in the conditional formatting is looped through in a For Each ... Next loop
the format type is tested for Cell Value Is, xlCellValue
the format operator is tested against each of the 8 possible values, xlEqual, xlNotEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlBetween, and xlNotBetween
depending upon the format operator, the range value is tested against the format value, or in the case of the between and not between operators, the pair of values.

IsCFMet1 caters for the Cell Value Is condition type, but another technique is required for the Formula Is condition type. The most difficult part of the Formula Is condition type is that Excel adjusts relative cell references to the active cell. As an example, assume that cell A1 has a conditional formatting formula of =A1>12. If the active cell is B1, within VBA the formula would be adjusted to =B1>12, which of course will not necessarily return the correct result when evaluated.

There is a rather neat way to get the formula re-adjusted, back relative to its base position. This is achieved using the ConvertFormula method. The method is called twice, first to convert the formula to R1C1 format, then convert it back to A1 format whilst adjusting it from the active cell back to the conditionally formatted cell.

There is one other point to note in evaluating formulae in conditional formatting. The technique above re-adjusts a conditional format formula back to its base cell but depends upon there being an anchor cell reference to facilitate that re-adjustment. Many formula have functions that do not have a cell anchor, using ROW() or COLUMN(), but these still need to be re-adjusted. My first thought was to substitute these with a reference to the conditional format cell, so that it says ROW(A7) say, and then pass this to the re-adjusting code. Unfortunately, passing a formula such as =A7=COLUMN(A7) to the Evaluate method causes a VBA failure, which I have yet to get to the bottom of. What I finally did was to substitute any occurrence of ROW() with the row of the conditionally formatted cell, and substitute any occurrence of COLUMN() with the column of the conditionally formatted cell.

Once the formula is re-adjusting back to the base cell, it is evaluated to determine whether that condition is satisfied or not, returning True or False.

The function is shown in Figure 3 below.

'---------------------------------------------------------------------
Public Function IsCFMet2(rng As Range) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlExpression Then
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.Row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            IsCFMet2 = rng.Parent.Evaluate(sF1)
         End If
         If IsCFMet2 Then Exit Function
     Next oFC
   End If

End Function

Fig. 3.

Combining the two functions, IsCFMet1 and IsCFMet2, we get a function that can return whether a cell is conditionally formatted, and whether any of its conditions are satisfied, whether a cell value of formula condition type is used. The combined function, IsCFMet, is shown in Figure 4 below.

This function will test a single cell only as mentioned previously.

'---------------------------------------------------------------------
Public Function IsCFMet(rng As Range) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  IsCFMet = rng.Value = oFC.Formula1
               Case xlNotEqual
                  IsCFMet = rng.Value <> oFC.Formula1
               Case xlGreater
                  IsCFMet = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  IsCFMet = rng.Value >= oFC.Formula1
               Case xlLess
                  IsCFMet = rng.Value < oFC.Formula1
               Case xlLessEqual
                  IsCFMet = rng.Value <= oFC.Formula1
                  IsCFMet = (rng.Value >= oFC.Formula1 And _
                             rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  IsCFMet = (rng.Value < oFC.Formula1 Or _
                             rng.Value > oFC.Formula2)
            End Select
         Else
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.Row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            IsCFMet = rng.Parent.Evaluate(sF1)
         End If
         If IsCFMet Then Exit Function
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

Fig. 4.

Testing Specific Conditions

So far we have created a multi-purpose function that can determine whether a cell is conditionally formatted or not, and whether any of its conditions are satisfied or not. Perhaps more useful would be able to test whether a particular attribute is set, or the value of a particular attribute.

As an example, a function that returns the format colour index of a conditionally formatted cell with a condition that is satisfied would be useful. Most of the previously developed function can be re-used, just returning the colour index when the formula evaluates to True.

This function, CFColorIndex, is shown below in Figure 5.

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  CFColorindex = rng.Value = oFC.Formula1
               Case xlNotEqual
                  CFColorindex = rng.Value <> oFC.Formula1
               Case xlGreater
                  CFColorindex = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  CFColorindex = rng.Value >= oFC.Formula1
               Case xlLess
                  CFColorindex = rng.Value < oFC.Formula1
               Case xlLessEqual
                  CFColorindex = rng.Value <= oFC.Formula1
               Case xlBetween
                  CFColorindex = (rng.Value >= oFC.Formula1 And _
                             rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  CFColorindex = (rng.Value < oFC.Formula1 Or _
                             rng.Value > oFC.Formula2)
            End Select
         Else
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.Row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            CFColorindex = rng.Parent.Evaluate(sF1)
         End If

         If CFColorindex Then
            If Not IsNull(oFC.Interior.Colorindex) Then
               CFColorindex = oFC.Interior.Colorindex
               Exit Function
            End If
         End If
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

Fig. 5

This can easily be extended to cater for either a cell colour or the font colour.

'---------------------------------------------------------------------
Public Function CFColorindex(rng As Range, _
                      Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            Select Case oFC.Operator
               Case xlEqual
                  CFColorindex = rng.Value = oFC.Formula1
               Case xlNotEqual
                  CFColorindex = rng.Value <> oFC.Formula1
               Case xlGreater
                  CFColorindex = rng.Value > oFC.Formula1
               Case xlGreaterEqual
                  CFColorindex = rng.Value >= oFC.Formula1
               Case xlLess
                  CFColorindex = rng.Value < oFC.Formula1
               Case xlLessEqual
                  CFColorindex = rng.Value <= oFC.Formula1
               Case xlBetween
                  CFColorindex = (rng.Value >= oFC.Formula1 And _
                             rng.Value <= oFC.Formula2)
               Case xlNotBetween
                  CFColorindex = (rng.Value < oFC.Formula1 Or _
                             rng.Value > oFC.Formula2)
            End Select
         Else
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.Row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            CFColorindex = rng.Parent.Evaluate(sF1)
         End If

         If CFColorindex Then
            If text Then
               If Not IsNull(oFC.Font.ColorIndex) Then
                  CFColorindex = oFC.Font.ColorIndex
               End If
            Else
               If Not IsNull(oFC.Interior.ColorIndex) Then
                  CFColorindex = oFC.Interior.ColorIndex
               End If
            End If
            Exit Function
         End If
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

Fig. 6

Other attributes can be tested just as easily. For instance, to determine whether a cell is bolded or not, remove the text option and the associated code, and replace the line

            CFColorindex = oFC.Interior.ColorIndex
with
            CFFontName = oFC.Font.Bold

To get the font style used, use

            If Not IsNull(oFC.Font.FontStyle) Then
               CFFontName = oFC.Font.FontStyle
            End If
etc.

Testing A Multi-cell Range

The previous examples show how to test an individual cell to determine whether it is conditionally formatted, and how to determine what condition is satisfied. Often it is useful to be able to test a range of cells and return an array of values. Whilst this is not specifically a function related to conditional formatting, it is useful and so is shown here.

The CFColorindex function shown previously is still used, we just create another wrapper function that processes the full range, and passes each cell within that range to that function. As mentioned, an array of values is returned. It should also be noted that single cells should still be catered for, that this requires separate logic within the function.

'---------------------------------------------------------------------
Public Function CFArrayColours(rng As Range, _
                        Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

   If rng.Areas.Count > 1 Then
      CFArrayColours = "#Too many areas!"
      Exit Function
   End If

   If rng.Cells.Count = 1 Then
      aryColours = CFColorindex(rng, text)
   Else
      aryColours = rng.Value
      i = 0
      For Each row In rng.Rows
         i = i + 1
         j = 0
         For Each cell In row.Cells
            j = j + 1
            aryColours(i, j) = CFColorindex(cell, text)
         Next cell
      Next row
   End If

   CFArrayColors = aryColours

End Function

Fig. 7.

A Conditionally Formatted Colour Counting Function

The final element of this discussion addresses a frequently asked topic, how can the number of cells within a range be counted for a certain colour value.

The function provided here is very similar to the multi-cell range example previously shown, it simply adds one for every true condition within the range, rather than passing back the values in an array. Again, the CFColorindex function is used with a wrapper function that does the summing.

'---------------------------------------------------------------------
Public Function CFColorCount(rng As Range, _
                      ciValue, _
                      Optional text As Boolean = False)
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryColours As Variant

   If rng.Areas.Count > 1 Then
      CFColorCount = "#Too many areas!"
      Exit Function
   End If

   If rng.Cells.Count = 1 Then
      CFColorCount = -CLng(CFColorindex(rng, text) = ciValue)
   Else
      i = 0
      For Each row In rng.Rows
         i = i + 1
         j = 0
         For Each cell In row.Cells
            j = j + 1
            CFColorCount = CFColorCount - _
                          CLng(CFColorindex(cell, text) ) = ciValue)
         Next cell
      Next row
   End If

End Function

Fig. 8

This is used in the following way

=CFColorCount(A1:A4,38)

which tests how many cells in A1:A4 are conditionally formatted with a ColorIndex of 38.

Another way is

=CFColorCount(A1:A4,CFColorindex(A1))

which uses the CFColorindex function to translate a colour to its ColorIndex value.

Further Enhancements

The examples presented here assume that if the format type is Cell Value Is, the value being tested against is a constant. Unfortunately, cell references can also be used, which require different logic, so the functions should also cater for this. The technique used for the Formula Is type can also be deployed here.
The conditional formatting colour counting function could be combined with code to calculate a cells manually formatted colour to provide a function that can count both. Better, create a function that can count conditionally formatted colours, manually formatted colours, or both.
If a conditional formatting condition refers to another cell, any changes to that referred to cell will update the conditional format, but will not cause any of the previously given functions to recalculate. This is easily overcome by adding Application.Volatile at the start of the code.

References

Chip Pearson, who has one of the best Excel reference sites (http://www.cpearson.com) was the first person that I know of to address testing conditional formatting conditions, and it was this that inspired me to write this paper.

Excel' s built-in conditional formatting is constrained to three conditions. To provide more, worksheet event code is required, or use the CFPlus add-in, a free add-in that can be obtained here.

Acknowledgements

I had a productive online discussion with Harlan Grove in the Microsoft Excel newsgroups regarding the handling of formulae in a condition. These discussions have been incorporated into the solutions presented here.

Downloads

This page can be downloaded as a zipped Word document here (41Kb)