Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 22nd 04, 04:07 PM
Beth H
 
Posts: n/a
Default 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   Report Post  
Old November 22nd 04, 04:15 PM
JulieD
 
Posts: n/a
Default

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   Report Post  
Old November 22nd 04, 05:06 PM
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Old February 26th 05, 08:03 AM
Pauline
 
Posts: n/a
Default

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   Report Post  
Old February 26th 05, 12:12 PM
Ken Wright
 
Posts: n/a
Default

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

--
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   Report Post  
Old February 26th 05, 01:01 PM
Bob Phillips
 
Posts: n/a
Default

"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   Report Post  
Old February 27th 05, 02:43 AM
Pauline
 
Posts: n/a
Default

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   Report Post  
Old February 27th 05, 10:24 AM
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Old September 19th 05, 04:48 PM
Suseela
 
Posts: n/a
Default

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   Report Post  
Old September 19th 05, 06:43 PM
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 07:03 PM
Extend conditional formatting to more than 3 conditions Danny Excel Worksheet Functions 1 November 10th 04 11:57 AM
How do I use conditional formatting for multiple rows? Jim Johnson Excel Worksheet Functions 1 October 30th 04 03:36 AM


All times are GMT +1. The time now is 02:47 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017