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
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
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.
Jim,
I did consider that and you’re right, it would have been more efficient.
Unfortunately, one Area may contain many different Conditional Formats.
Alex,
Excellent – that’s along the same lines as what I did.
Cheers,
Rob
has anyone used this:
http://www.xldynamic.com/source/xld.CFPlus.Download.html
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?
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
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
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