More than 3 Conditional Formatting Conditions

[Beth H]

I'm aware that excel 2000 limits conditional formatting to 3 conditions. I
would like to use 8. I have a column with data validation applied in which
the user can only select and enter 1 of 8 values. If they enter "DW", then
fill color = blue; if "O" then fill color = purple; if "AB", then fill color
= orange; if "ZY" then fill color = gray.... etc. How can I get around the
limit of 3 conditional formatting conditions?

 

[JulieD]

Hi Beth

couple of options:

- John McGimpsey has an article on doing up to 6
www.mcgimpsey.com/excel/conditional6.html

- Bob Phillips & Frank Kabel have developed an excel add-in
www.xldynamic.com/source/xld.CFPlus.Downlaod.html

- or you can use some code in the worksheet_change event (paste in the
"sheet module" of the sheet -
right mouse click on the sheet tab and choose view / code you should see on
the top left of the VBE window your file name in bold (if not try view /
project explorer) and the sheet that you were on selected ... that's the
"sheet module" ... if the wrong sheet is selected then just double click on
the correct one on the right you should see some white space - copy & paste
the code in
there -

assuming you want the conditional formatting to work on cell B6
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell. If you want to use text, replace
the numbers (1 - 9) with the text in quotes (e.g. "cat", "dog" etc)

Hope this helps
Cheers
JulieD

 

[Frank Kabel]

Hi Julie
[...]

- Bob Phillips & Frank Kabel have developed an excel add-in

www.xldynamic.com/source/xld.CFPlus.Downlaod.html

Thanks for mentioning ourt addin. Just a small typo in the URL
Here's the correct one:
http://www.xldynamic.com/source/xld.CFPlus.Download.html

Frank

 

[Pauline]

Hey Julie,

Thanks for this. I tried the XLD Tools program however it only does the
changes for what is currently displayed - if you change the contents the
colour doesnt change as well.

Im not trying your worksheet_change and its working nicely (though a little
slow - but its a big spreadsheet). Can you change the cell colour as well
using this method or only the font colour?

Thanks,

Pauline

 

[Ken Wright]

You can change pretty much most things wrt a cell's contents or appearance.
Typical way of doing this if you have multiple scenarios would be to use a
Select Case structure as decsribed by Julie. If you want to add other
elements to what you want changed then consider something like this. Note
that you can specifically set the area (and in fact need to with this
example), eg

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim rng As Range
Set rng = Range("A1020")
Application.EnableEvents = False
On Error GoTo CleanUp
If Not Intersect(Target, rng) Is Nothing Then
For Each oCell In rng
With oCell
Select Case .Value
Case Is = "Tom"
.Interior.ColorIndex = 1
.Font.ColorIndex = 3
Case Is = "Dick"
.Interior.ColorIndex = 4
.Font.ColorIndex = 6
Case Is = "Harry"
.Interior.ColorIndex = 5
.Font.ColorIndex = 9
Case Is = "Fred"
.Interior.ColorIndex = 7
.Font.ColorIndex = 10
Case Else
.Interior.ColorIndex = xlNone
End Select
End With
Next oCell
End If
CleanUp:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Hey Julie,

Thanks for this. I tried the XLD Tools program however it only does the
changes for what is currently displayed - if you change the contents the
colour doesnt change as well.

What exactly do you mean by that statement. CFPlus works like (b ut for more
conditions and more formats) regular CF.

 

[Pauline]

Hey Bob,

I use drop down boxes. I find that the XLD Tools program will give the
correct conditional formatting but only 'at the time' that i process the
formatting request. It is not ongoing.

I use drop down boxes with colour differentiators. Lots of them (over the
10,000 amount actually) so i had to reduce the size of the spreadsheet to see
if that would then still work. When i use the program it correctly changes
the colour for the drop down boxes with a selection, if i then choose a drop
down box it doesnt remember to change it. In fact it has no memory of the
conditional formatting at all (so if i want to set it up again i have to
start from scratch).

Cheers,

Pauline.

 

[Bob Phillips]

Pauline,

This doesn't sound right. Couple of questions.

How did you install CFPLus?

What Excel version are you using?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Suseela]

Hi,
Could I know how I can use this feature. Do I need to wirte a macro to this.
I installed the CFPlus. How do I generate more than 4 conditional formats.
Procedure for using this.

Thanks,
Suseela

 

[Bob Phillips]

No need for a macro, CFPlus has all the code that you need.

Go to the xldTools menu, select CFPlus, then Launch CFPlus. This will ask if
you want to activate the worksheet, which means adding code to it, and if
you say yes, you then get a dialog box to enter the conditions and formats.

 

[Christina]

Bob,

I have recently downloaded the CFPlus add-in. It seems like a great tool,
except I can't get it to work, nor access the help files. Is there somewhere
where you can direct me to a user's guide?

I've set my data range with the conditional formats, but nothing actually
physically changes? Please let me know where I can get some assistance as
this seems like a great utility.

Thanks,
Christina

 

[Adam]

I got the same problem. I'm trying to set up a bunch of formats then change
the cell value with a dropdown list. It doesn't seem to want to change it.
Please let me know if there's a fix.

Adam

 

[TC]

I just d/l'ed CFPlus and it seems like a nice tool but I am applying the
conditional formatting to merged cells which are now large drop-down list.
Upon selection of the item, the cells in the drop-down list un-merge. If not
for that bug this would be perfect.