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

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.

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)

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.

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.

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

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.

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

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

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

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

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

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.

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

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

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.

- 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.

**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.

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.

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