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