I recently complained about listing conditional formatting in 2007. Now it’s time to do 2003.
Excel 2003 was a little easier in some ways and harder in others. Far less “Types” to worry about, but no AppliesTo property. So I had to go cell by cell and check for FormatConditions. Whenever my code starts to get messy, it’s time for a class or two.
Dim cf As Variant
Dim rCell As Range
Dim rSame As Range
Dim colFormats As Collection
Dim i As Long
Dim wsOutput As Worksheet
Dim rNext As Range
Dim clsCondForms As CCondForms
Dim clsCondForm As CCondForm
10 Set wsOutput = ThisWorkbook.Worksheets.Add
20 wsOutput.Range(“A1:E1”).Value = Array(“Type”, “Range”, “StopIfTrue”, “Formual1”, “Formual2”)
30 Set clsCondForms = New CCondForms
40 For Each rCell In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
50 For i = 1 To rCell.FormatConditions.Count
60 Set clsCondForm = New CCondForm
70 Set clsCondForm.FormCond = rCell.FormatConditions(i)
80 Set clsCondForm.AppliesTo = rCell
90 If clsCondForms.Exists(clsCondForm) Then
100 clsCondForms.CondForm(clsCondForm.CondFormID).Merge clsCondForm
120 clsCondForms.Add clsCondForm
130 End If
140 Next i
150 Next rCell
160 For i = 1 To clsCondForms.Count
170 Set clsCondForm = clsCondForms.CondForm(i)
180 wsOutput.Cells(i + 1, 1).Resize(1, 5).Value = clsCondForm.WriteArray
190 Next i
Let’s see what it does line-by-line. Line 10 adds a new worksheet to the workbook. This will be where the results are displayed. Line 20 puts the headers on the worksheet.
I have two classes, CCondForm and CCondForms. The latter is the parent of the former. That is, CCondForms is a collection of a bunch of CCondForm objects. We’ll dig into the classes, but for now, in Line 30, I’m creating a new CCondForms object so I have a place to store all the CCondForm objects I’ll be creating shortly.
Staring in Line 40, I’m looping through all the cells that have conditional formatting. Then I’m looping through all of the FormatConditions in each cell. For every one that I find (each cell can have up to three), I create a new CCondForm object. In the end, however, I don’t want a CCondForm object for every cell. Rather, I want one for every range that has the same FormatCondition. So while I create a CCondForm object now, I’ll only add it to the CCondForms collection if it’s “new”.
CCondForm only has two read/write properties; FormCond, a FormatCondition, and AppliesTo, a Range. In Lines 60-80 I create a new CCondForm object and write it’s two properties. Because they are objects (not strings or longs or doubles) I use the Set keyword.
Now that I’ve established my new CCondForm object, I need to see if I already have one in the collection. There doesn’t appear to be any way to uniquely identify a FormatCondition. I ended up concatenating Formula1 and Formula2. I figured if those two properties are the same, then it’s the same FC. I’m sure there’s a gotcha in there somewhere. In Lines 90-130, I check to see if there’s a match. If there is, I combine the two objects into one. If there isn’t, I add it to the collection.
Dim bReturn As Boolean
Dim clsTemp As CCondForm
Dim i As Long
bReturn = False
For i = 1 To Me.Count
Set clsTemp = Me.CondForm(i)
If clsTemp.CondFormID = clsCondForm.CondFormID Then
bReturn = True
Exists = bReturn
The Exists property loops through all the CCondForm objects and looks for one with the same CondFormID. If it finds one, that means there’s a match and Exists returns TRUE. CondFormID is a read only property (there’s a get, but no let) and looks like this.
CondFormID = Me.Formula1 & Me.Formula2
Public Property Get Formula1() As String
On Error Resume Next
Formula1 = “‘” & Me.FormCond.Formula1
Public Property Get Formula2() As String
On Error Resume Next
Formula2 = “‘” & Me.FormCond.Formula2
I got tired of On Error-ing (you get an error if there’s not Formula2) so I just made custom properties that return Formula1 and Formula2, or an empty string if there isn’t one. If a match is found, the new CCondForm and the existing CCondForm are merged via the Merge method. It’s a dandy.
Set Me.AppliesTo = Union(Me.AppliesTo, clsToMerge.AppliesTo)
Yep, not much to that. If the CF is the same, I just make the AppliesTo range bigger by Unioning the existing range with the new one. If A1 is already in there with
=MOD(ROW(),2)=1, and A2 has the same CF, the Merge simply makes the AppliesTo property of the existing CCondForm object A1:A2. Then A1:A3 and on and on until it runs out of cells.
By the time I get to Line 160, I have a number of CCondForm objects in my CCondForms collection object (Three in this example). I loop through them and use the read-only property WriteArray to dump the relevant data to a range.
Dim aReturn(1 To 1, 1 To 5) As Variant
aReturn(1, 1) = Me.CfType
aReturn(1, 2) = Me.AppliesTo.Address
aReturn(1, 3) = True
aReturn(1, 4) = Me.Formula1
aReturn(1, 5) = Me.Formula2
WriteArray = aReturn
Public Property Get CfType() As String
If Me.FormCond.Type = 1 Then
CfType = “Cell Value”
CfType = “Expression”
Nothing fancy, just abstracting it out of my main code and into a class. I put StopIfTrue as True for everything. I don’t know if that’s the case in 2003, but I think it is. It’s irrelevant because you can’t change it, but I wanted to keep the output consistent with my previous blog post.