Listing Format Conditions
I wrote some code to list out all the conditional formatting rules in a worksheet. It wasn’t as easy as I thought it would be. In Excel 2007, MS introduced some new format conditions like Icon Sets and Databars that complicate things. As a result, I didn’t get too fancy with the output.
Dim cf As Variant
Dim rCell As Range
Dim colFormats As Collection
Dim i As Long
Dim wsOutput As Worksheet
Set colFormats = New Collection
For Each rCell In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
For i = 1 To rCell.FormatConditions.Count
On Error Resume Next
colFormats.Add rCell.FormatConditions.Item(1), rCell.FormatConditions(1).AppliesTo.Address
On Error GoTo 0
Next i
Next rCell
Set wsOutput = Workbooks.Add.Worksheets(1)
wsOutput.Range(“A1:E1″).Value = Array(“Type”, “Range”, “StopIfTrue”, “Formual1″, “Formual2″)
For i = 1 To colFormats.Count
Set cf = colFormats(i)
With wsOutput.Cells(i + 1, 1)
.Value = FCTypeFromIndex(cf.Type)
.Offset(0, 1).Value = cf.AppliesTo.Address
.Offset(0, 2).Value = cf.StopIfTrue
On Error Resume Next
.Offset(0, 3).Value = “‘” & cf.Formula1
.Offset(0, 4).Value = “‘” & cf.Formula2
On Error GoTo 0
End With
Next i
wsOutput.UsedRange.EntireColumn.AutoFit
End Sub
And I got output that looks like this
I couldn’t figure an easy way to loop through all the conditional formatting on a sheet. The FormatConditions collection is a property of the Range object not the Worksheet object. So I looped through all the cells with conditional formatting using SpecialCells. Then to remove the duplicates, I put the FormatCondition object into a collection keyed on the AppliesTo address. Collections don’t allow duplicate keys, so I only get one entry for that FormatCondtion that applies to G5:G10. It’s not fool-proof though. I could have two FormatConditions that apply to the same range and only the first would be taken. That was problem #1.
The second problem was that all conditional formatting aren’t FormatConditions. I put a ColorScale on my sheet and it didn’t like
The FormatConditions.Item property doesn’t return a FormatCondition object, but a ColorScale object. Many of the properties are the same between the FormatCondition and ColorScale objects so I changed cf to a Variant.
I only listed the Formula1 and Formula2 properties if cf was a FormatCondition and ignored all the other types of objects cf could be. It would take too long to list out all the parameters for all the object types and I lost interest.
To convert the Type to a string, I used this function
Select Case lIndex
Case 12: FCTypeFromIndex = “Above Average”
Case 10: FCTypeFromIndex = “Blanks”
Case 1: FCTypeFromIndex = “Cell Value”
Case 3: FCTypeFromIndex = “Color Scale”
Case 4: FCTypeFromIndex = “DataBar”
Case 16: FCTypeFromIndex = “Errors”
Case 2: FCTypeFromIndex = “Expression”
Case 6: FCTypeFromIndex = “Icon Sets”
Case 14: FCTypeFromIndex = “No Blanks”
Case 17: FCTypeFromIndex = “No Errors”
Case 9: FCTypeFromIndex = “Text”
Case 11: FCTypeFromIndex = “Time Period”
Case 5: FCTypeFromIndex = “Top 10″
Case 8: FCTypeFromIndex = “Unique Values”
Case Else: FCTypeFromIndex = “Unknown”
End Select
End Function
I wish there was a better way to get at enumerations.
Then, of course, I wanted to show an example of the conditional formatting. Well, that wasn’t going to happen. In conclusion, Excel 2007 conditional formatting is great, but trying to recreate the built-in conditional formatting dialog box is the opposite of great.
It gets even more interesting, if you overlap two areas with separate CF’s.
XL2007 will double count the number of FormatConditions in the overlapped areas.
XL2003 on the other hand does not.
The reason appears to be that the SpecialCells(xlCellTypeAllFormatConditions) method returns separate (non overlapped areas) in xl2003 and xl2007 does not.
I got around this (I hope) in my Formats & Styles add-in (in xl2007) by iterating every cell in a rectangular range that encompassed the returned areas.
Be aware… “It can’t be bargained with. It can’t be reasoned with. It doesn’t feel pity, or remorse, or fear. And it absolutely will not stop, ever, until you are dead.”
‘–
Formats & Styles (free)… http://excelusergroup.org/media/p/4861.aspx
[...] took me a long time to figure everything out. If only I had written something like this script, I could list all the conditional formatting rules one by one, and understand it all much [...]
I just tried without success Chip Pearson’s code in XL2007 : http://www.cpearson.com/excel/cfcolors.htm
Chip’s code was written for Excel 97-2003. Conditional Formatting was completely revamped in 2007.
This is exactly what I need, but how could I get it to work for Excel 2003?
I have a lot of CF’s in an inherited workbook, and I need to make sure that they are all corrected pointed to the relevant cells.
A sheet that gives the information as shown above woudl save hours of work – if it is possible.
I’ve been looking at conditional formatting as a faster way of applying complex formulae on large amounts of data. The result is blindingly fast but I can’t get access via vba to the fill colour that I’ve applied to the cell. Because the whole range has essentially the same formula applied, when I drill down to look at the cells conditionalformat to check it’s result, they are all the same!
Has anyone managed to find a way around this? Any help much appreciated.
Update in case anyone else has a similar issue…I’ve used Chip Pearson’s code which seems to still work fine in 2007 but have updated the lines where Case xlExpression is true to evaluate a string based on the formula within the conditional formatting but using the current row value rather than the default row value applied to the whole column. Works like a dream