Conditional Formats Manager

I’ve been rewriting some user spreadsheets.

One thing I noticed after a while was that there isn’t an easy way to manage Conditional Formats.
As you may know, Conditional Formats use formulas too, so they can be very important to get right – especially if these cells are relied on to highlight errors.

Excel provides some limited ways to manage Conditional Formats.
There’s the editor itself: from the Format menu, Conditional Formatting…
and then there’s the Goto feature: from the Edit menu, Go To…, Click Special…, Select ‘Conditional Formats’ (you then have a choice between All or Same).

Those can be challenging to use if you’re dealing with a sheet full of various formats.

I put together a userform to list all of the Cells which contain Conditional Formats.
If groups of Cells contain the same Conditional Format then they appear in the same list item.

Click a List Item to select the cells.
Doubleclick a List Item to edit the Conditional Format.

I’ve not provided an XLA add-in – I’ll leave that to you. Here are instructions for building the userform.

Create a new Userform and drop a ListView control onto it. Rename the ListView control: lvwAddress
(Don’t have a ListView control in your Toolbox? Right-click in the Controls area, select Additional Controls…, tick Microsoft ListView Control)
Open the code for the Userform and drop the following code in:

Private col As New Collection
 
Private Sub UserForm_Initialize()
    Const cCaption = “Conditional Formats”, cKey = “KeyID”
 
    Dim bln As Boolean, str As String, i As Long
    Dim rngAll As Range, rng As Range, rngSel1 As Range, rngSel2 As Range
 
    Me.Caption = cCaption
 
    On Error Resume Next
    Set rngAll = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
    On Error GoTo 0
 
    If Not rngAll Is Nothing Then
        i = 1
        For Each rng In rngAll
            Set rngSel1 = rng.SpecialCells(xlCellTypeSameFormatConditions)
            str = rngSel1.Address
            bln = False
            For Each rngSel2 In col
                If str = rngSel2.Address Then
                    bln = True
                    Exit For
                End If
            Next
            If Not bln Then
                col.Add Item:=rngSel1, Key:=cKey & ” “ & i
                i = i + 1
            End If
        Next
 
        With lvwAddress
            .ColumnHeaders.Add Text:=“Address”, Width:=.Width – 17
            .View = lvwReport
            .FullRowSelect = True
            .HideSelection = False
            .LabelEdit = lvwManual
 
            For i = 1 To col.Count
                .ListItems.Add Text:=col(i).Address(False, False), Key:=cKey & ” “ & i
            Next
 
            .Sorted = True
        End With
    End If
End Sub
 
Private Sub lvwAddress_ItemClick(ByVal Item As MSComctlLib.ListItem)
    col(lvwAddress.SelectedItem.Key).Select
End Sub
 
Private Sub lvwAddress_DblClick()
    Application.Dialogs(xlDialogConditionalFormatting).Show
End Sub

You would run the userform with this statement:

    Userform1.Show

It’s interesting to note that some simple tweaks to the above code would provide you the same management of Data Validations:
1. Change the value for the Const cCaption
2. Change xlCellTypeAllFormatConditions to xlCellTypeAllValidation
3. Change xlCellTypeSameFormatConditions to xlCellTypeSameValidation
4. Change xlDialogConditionalFormatting to xlDialogDataValidation

Posted in Uncategorized

9 thoughts on “Conditional Formats Manager

  1. Good idea, Rob. I think it might be possible to make the routine a little more efficient by looking at each area in rngAll rather than each cell:

    >> For Each rng In rngAll.Areas

    since each cell in each area would have the same conditional formatting. This could pay off if you have large block(s) of cells with the same format.

    Let me know if I’m missing something…

    Jim

  2. Nice, Rob.
    I put the function in my Personal.xls, with an optional selection between Conditional Format and Data Validation. I use a named parameter in the target file to track the last choice between CF and DV.

    I think this will be very useful.

  3. Rob,
    I’ve also made the UF modeless. This turns out to be a really nice little utility.

    BTW, I would like to learn more about the use of the ListView control in VBA. The only “here’s how” type references I can find are on the MS VB6 knowledge base – but they are not particularyly easy to use.

    Any suggestions for a comprehensive intoduction to ListView properties and methods, plus some examples?(Could also ask for same on TreeView and other ActiveX controls)

    J-Walk – would this be a good chapter?

  4. Alex,

    I think the ListView control is great. I use it instead of the Forms list control. I tend not to use TreeView that often. Not a lot of my database apps need the hierarchy.

    These controls are part of a family called ‘Common Controls’. I believe these are installed with the VB6 Runtime, installed by default with Windows XP – can anyone confirm this?

    For more info, you could search for ListView or TreeView on MSDN.
    I came across this old article for Office Developer Edition.
    Following that, Google Groups

    Cheers,
    Rob

  5. Hello,

    since your talking about conditional formatting here i assume i can post my question here. I am trying to use conditional formatting with thick borders instead of normal ones. I can’t figure out how to do that. I tried to make a macro but still get faillures. Can anyone help me

    A desperate dutch excel user
    cheers Jurgen

  6. Jurgen,

    Conditional Formats do not currently support that many Border weights.

    You could trick the cell by doing a reverse condition.

    Instead of normal cell thin border with conditional formats thick border
    Negate the condition, and make normal cell thick border, conditional formats thin border.

    Rob

Leave a Reply

Your email address will not be published. Required fields are marked *