I’m working on an application that Project Managers use to test their competencies against a competency framework. There’s a lot of questions and I don’t want to freak them out by showing all at once, so I use Conditional Formatting to unhide the questions in batches, as well as to highlight the next thing that needs to be answered, and to show how their self assessment corresponds to the ratings key.
Here’s how it looks before users start filling it out: only the first batch of questions relating to the first competency area are displayed:
As they complete it, their answers get color-coded to match the key:
When they complete the last question in regards to the current competency they’re working on, the next batch of questions for the next competency appears, and the definition for that competency overwrites the previous one in the top left:
I’m using CF because I don’t want to use code to do this if I can help it, so I don’t have to deal with security settings or questions about enabling macros. Plus I’m just there for 4 weeks, and there isn’t going to be a VBA developer picking up the project after me.
But the CF dialog – how shall I put this delicately – seems as if it was built by Microsoft. (Ok, so I threw tact to the wind there. It’s a dog, a pig, and a slug, all rolled into one needy, dirty, slimy son-of-a-bitch pest.) Here’s all it shows me in all of its non-resizable gory. (Yes, gory, not glory.):
Using it is like trying to build a ship in a bottle inside of another ship in a bottle. Now I know how proctologists feel. If it was half the dialog box that the Name Manager was, it would at least yet you resize so you could see all your conditions at once:
Just look what the Name Manager gives us, by way of comparison:
The Name Manager gives us friendly names, notes where we can record what does what, resizable columns, and a resizable dialog. Compared to that, the CF dialog is like the punchline from a Monty Python skit.
“Oh, we used to dream of having a resizable dialog box.”
What’s worse, I keep noticing that the order of my CF conditions gets scrambled – maybe when I copy the sheet to a new template, maybe when I insert or delete columns, maybe when I don’t look suitably impressed whenever it does work correctly. I don’t know.
So I thought I’d look for a way to back them up. I looked at Dicks’ posts Listing Format Conditions and Listing Format Conditions Redux, but those would need more work still in order to record the formatting, and then a routine to restore everything from the storage table.
But then I discovered this: If you fire up the macro recorder, bring up the CF dialog box, select Manage Rules, select Show formatting rules for This Worksheet, and then makes some trivial change – such as click the down arrow to move the currently selected CF condition down one, then click the up arrow to put it back:
…then as soon as you push OK the Macro Recorder spits out a macro that clears all the format conditions on the entire sheet, and then builds those CF rules again from scratch. All of them. Here’s an excerpt:
Sub Macro7()
Cells.FormatConditions.Delete
Range("PM[Definition " & Chr(10) & "(Experience)]").Select
Range("G14").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ISBLANK($I14),$T14=FALSE)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("PM[Definition " & Chr(10) & "(Knowledge)]").Select
Range("G14").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISBLANK($G14)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
End Sub
That – my friend – is your CF backup. The only thing wrong with this code is that it keeps putting the line Range(“G14”).Activate in the middle of each block for some strange reason. That happened to be the cell I had selected when I recorded the macro. But if you do a find and replace on the code to ditch it in favor of a more helpful “” then the code works fine. (Yes I know that CF is relative, but that’s still no reason why it should select the cell I have selected and then apply the formatting to it rather than to the desired target). (Edit: You need this activation if you are dealing with CF conditions with relative referencing. But it doesn’t work properly in the event that you originally applied CF to a range while you had another range outside of that CF area selected. The CF manager takes account of this properly, but the macro output doesn’t, meaning it selects a range, then activates a cell/range outside of that selection, which screws things up. So it pays to eyeball the code for situations like this.)
Now what would be cool is if someone out there was to code up a routine that uses extensibility to read this from the VBE and populate a handy table in the worksheet with all the parameters. Extra credit to someone who devises a way to make the macro recorder fire up and do this automatically for each sheet.
Even better: it would be amazing if Microsoft would show the CF dialog the slightest amount of love. CF could be one of the most powerful features of Excel for non coders. But not as it stands at the moment. Right now, it’s a pigdogslug.
An even easier way
So I got to thinking that if Excel will quickly spit out a complete list of FormatConditions at the ‘AppliesTo’ level for the sheet, it must have them stored in a collection somewhere. And that collection is…drum roll please…the Cells object.
Want some code to quickly and easily print out every format condition everywhere? Here ’tis:
Sub FindCF()
'We have to Dim WhatIsIt as a generic Object instead of declaring as FormatCondition because DataBars screw things up.
'See http://excelmatters.com/2015/03/04/when-is-a-formatcondition-not-a-formatcondition/
Dim WhatIsIt As Object
Dim fc As FormatCondition
Dim db As Databar
Dim cs As ColorScale
Dim ics As IconSetCondition
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each WhatIsIt In ws.Cells.FormatConditions
Select Case TypeName(WhatIsIt)
Case "Databar":
Set db = WhatIsIt
Debug.Print "Type: " & "DataBar" & vbTab & "Applies To: " & db.AppliesTo.Address
Case "FormatCondition"
Set fc = WhatIsIt
Debug.Print "Type: " & "FormatCondition" & vbTab & "Applies To: " & fc.AppliesTo.Address & vbTab & "Formula: " & fc.Formula1
Case "ColorScale"
Set cs = WhatIsIt
Debug.Print "Type: " & "ColorScale" & vbTab & "Applies To: " & cs.AppliesTo.Address
Case "IconSetCondition"
Set ics = WhatIsIt
Debug.Print "Type: " & "IconSet" & vbTab & "Applies To: " & ics.AppliesTo.Address
Case Else
Stop
End Select
Next WhatIsIt
Next ws
End Sub
Code? Luxury!
A while back I wrote a few posts that really got me digging around in the conditional formatting object model. I was just looking at this one – http://yoursumbuddy.com/vba-formatconditions-per-row-colorscales-databars-and-iconsets/ – and I must say it’s beautiful :)
Ah. Where was that post earlier today when I could have used it, eh?
So here’s something I just realized: the FormatConditions collection is a member of the Cells object, and at that lever each FormatCondition only gets listed once. Dick’s previous approach looked for FormatConditions by iterating through each cell that had one, leading to multiple matches. But you can actually do it with a simple For Each SomeObject In ws.Cells.FormatConditions. Have amended the original post above.
As long as you are not using one of the binary file formats, you can also get a full list of the conditional formatting used in a worksheet by looking at the file’s XML. Make a copy of your .XLSX file, rename as .ZIP and look for the tags in the worksheet XML files under e.g. \XL\Worksheets\sheet1.xml.
Not as nice an approach as your macro, but it does give you every conditional formatting property including the colours etc.
Jeff, this is a beauty. Now someone just needs to package it into a useful add-in like we used to have to use for range names. That will prompt MS to finally create a usable dialog, and the poor sucker who wrote the add-in will be out all that work.
Oh, was that cynical? Sorry.
Jeff, you left out ColorScales and IconSets.
Thanks for the reminder. Have updated the routine.
Jeff,
This is really great stuff. I use conditional formatting up the wazoo and I try and keep my spreadsheets consistent – now I can. Thank you.
I had three questions, if you have the time/inclination (and please point me elsewhere if there’s a good pre-existing resource – I’m a bit of a noob):
1) Instead of outputting to the Immediate window, how would I make the output into an array so that I then could turn that array into a table?
2) I’m attempting to use either Rick Rothstein’s or Chip Pearson’s approach to output the RGB values of the CF text & fill; however, I noticed that .AppliesTo.Address is not a range. Any suggestions on how to convert the string into a range so I can use it as input into these color functions?
3) Are you aware of any way to reverse this process? By that I mean, if I generate a list of condition type, ranges, and formula, is there anyway to read those CF parameters stored in a worksheet and apply it to other worksheets?
Thanks much.
Good questions, and ones that I have in my sights. Really busy at present, but I’ll take a look in the coming week.
Jeff I love this post!! The ship in a bottle and proctologist bits are magic. Says it all and hope MS have this logged for a fix one day, that dialog is a cruel ordeal to use.
But I also love this post because it has a practical answer to a project I am doing now with complex CF within a table that doesn’t survive rows and columns being deleted or added or moved. So I couldn’t wait to try your discovery but it wouldn’t work after trying multiple times. But then I realised I was trying to do this for CF settings within a Table and that was the difference to your example. So I tried changing select Show formatting rules for Table. Still didn’t work. Then I tried converting the table to a range then used the macro recorder (of course back to Show formatting rules for This Worksheet) and there was your discovery. Bit of a shame you have to convert the table to a range then back to a table to get this but it appears to come back to the way it was and anyway you don’t have to save the WB after you grab the code.
Thanks a lot for this one Jeff.
John
Hi John. Have you tried the Sub FindCF() routine?
Hello there Jeff
I hadn’t, as I was focused on a way to back up the settings, more than seeing them. But have tried it now, and WOW!!. Works perfectly and on the table settings – no need to convert the table back to a range. I think code like this that cuts out major frustration deserves special accolade. It’s like you fought the good fight with this one Jeff. You did this for your buddies. Now we can check what has been set up sensibly and copy and paste from other CF rules easily. It also gets me thinking, the next step is saying no more to the CF dialog and setting up the CF formulas in the worksheet so you access the normal formula editing, debugging and help features. Then VBA can suck these up to the CF rules. OK maybe you would still sneak into the CF dialog for the colours and fonts settings.
Thanks very much for sharing this.
John
Glad it helped, John. Yeah, those kind of features are on my ‘to do’ list and will ultimately end up in a book I’m near the end of writing.
Like many, I’ve been frustrated when my carefully crafted conditional formatting becomes bifurcated after innocently performing a copy/paste or some other such sin. And Excel’s miserable Conditional Formatting Rules Manager makes it a chore to fix the mangled rules. So I’ve written macros to backup and restore the active sheet’s conditional formatting using named ranges that auto-adjust to row/column changes.
There’s too much VBA code to post in this comment, but it is available from Google Drive here: https://drive.google.com/file/d/1GTauCG5kWPMz92YDrjnvGZfPpM3pHBe0/view?usp=sharing (click the download arrow in Drive’s upper-right corner). The zip file contains ReadMe.txt plus two VBA module files: M_CFBackup.bas and M_CFSupport.bas. Both module files can be opened for review in a text editor such as Notepad or Word.
M_CFBackup.bas contains two Excel macros CFBackup and CFRestore.
CFBackup is a macro to backup the active sheet’s conditional formatting (CF) using named ranges. Here’s a synopsis of CFBackup:
+ Create a new backup sheet
+ Define a named range with Workbook scope relating the backup sheet to ActiveSheet
- A named range will auto-adjust if ActiveSheet is renamed
+ Add a defined name with ActiveSheet scope to register information about the backup
+ Define a named range with ActiveSheet scope for each CF rule's AppliesTo range
- A named range will auto-adjust if rows or columns are inserted or removed
+ For each CF rule:
- Locate the first cell in the rule's AppliesTo range
- If the first cell is merged, copy it to a temporary cell before it is unmerged
- Copy the first cell's CF rule(s) to the backup sheet
- Isolate one unique rule from the CF rule(s) copied to the backup sheet
- Record the rule's defined name, range formulas, etc., on the backup sheet
+ Protect the backup sheet
CFRestore is a macro to restore the active sheet’s conditional formatting (CF) using a backup created by CFBackup. Here’s a synopsis of CFRestore:
+ Delete all CF rules on ActiveSheet
+ For each unique CF rule on the backup sheet:
- Copy the CF rule to a temporary cell on ActiveSheet
- Modify the temporary cell's AppliesTo range to match the CF rule's named range (auto-adjusted)
- Delete the temporary cell
+ Protect the backup sheet (if appropriate)
M_CFSupport.bas contains two user-defined functions (UDFs) NameRefersTo and IsProtected, one VBA support function ProgressBar_Text, one macro UnhideNames, and two UDF register procedures.
NameRefersTo(Name, [Choice]) is a UDF to return elements of the RefersTo property of the defined name (named range) Name, which must be supplied as a text argument. See the module file for detailed description of the optional integer argument Choice. The default Choice is 0 to return the RefersTo property directly.
IsProtected([Choice], [Target]) is a UDF to return the protection status (True or False) of Target’s Worksheet or Workbook. The default Target is the cell referencing this function; otherwise, a cell’s address (like $A$1) or VBA Range such as Range(“$A$1”) can be specified. See the module file for detailed description of the optional argument Choice, which can be either integer or text. The default Choice is 0 or “contents” to determine if the Worksheet’s contents are protected.
ProgressBar_Text is a VBA support function to prepare a text string to indicate a macro’s progress using Excel’s status bar. See the module file for details.
UnhideNames is a macro to unhide all defined names (named ranges) in all worksheets of the active workbook or only in the active sheet. This is included because the defined names created by CFBackup are all hidden from Name Manager (Ctrl+F3) and Go To (F5).
I have tested CFBackup and CFRestore with many scenarios, but not all. If you discover any issues, please add a comment to this thread.
For the latest on CFBackup and CFRestore, see My Excel Toolbox.