Home |
Search |
Today's Posts |
#1
|
|||
|
|||
More than 3 Conditional Formatting Conditions
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? |
#2
|
|||
|
|||
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 http://www.xldynamic.com/source/xld.....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 "Beth H" wrote in message ... 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? |
#3
|
|||
|
|||
Hi Julie
[...] - Bob Phillips & Frank Kabel have developed an excel add-in http://www.xldynamic.com/source/xld.....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.....Download.html Frank |
#4
|
|||
|
|||
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 "JulieD" wrote: 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 http://www.xldynamic.com/source/xld.....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 "Beth H" wrote in message ... 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? |
#5
|
|||
|
|||
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("A10:D20") 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 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- <snip |
#6
|
|||
|
|||
"Pauline" wrote in message
... 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. |
#7
|
|||
|
|||
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" wrote: "Pauline" wrote in message ... 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. |
#8
|
|||
|
|||
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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
#9
|
|||
|
|||
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" wrote: 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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
#10
|
|||
|
|||
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. -- HTH Bob Phillips "Suseela" wrote in message ... 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" wrote: 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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
#11
|
|||
|
|||
More than 3 Conditional Formatting Conditions
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 "Bob Phillips" wrote: 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. -- HTH Bob Phillips "Suseela" wrote in message ... 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" wrote: 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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 3 Conditional Formatting Conditions
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 "Christina" wrote: 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 "Bob Phillips" wrote: 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. -- HTH Bob Phillips "Suseela" wrote in message ... 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" wrote: 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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More than 3 Conditional Formatting Conditions
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. "Adam" wrote: 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 "Christina" wrote: 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 "Bob Phillips" wrote: 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. -- HTH Bob Phillips "Suseela" wrote in message ... 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" wrote: 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) "Pauline" wrote in message ... 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" wrote: "Pauline" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) | |||
Extend conditional formatting to more than 3 conditions | Excel Worksheet Functions | |||
How do I use conditional formatting for multiple rows? | Excel Worksheet Functions |