Excel:Additional Conditional Formatting

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

flipinoluv

Thread Starter
Joined
Apr 16, 2004
Messages
21
I notice that you are limited to only 3 conditional formats. Is there a way to add more?

Thanks
 
Joined
Aug 30, 2003
Messages
2,702
There's a workround for 6 font colours. For >3 fill colours you have to use code. Which are you after?

Rgds,
Andy
 

flipinoluv

Thread Starter
Joined
Apr 16, 2004
Messages
21
Yes, that is correct. I am looking for code that would include formatting for a minimum of 4 colors. Thanks.
 
Joined
Aug 30, 2003
Messages
2,702
That was an either/or question. Since you said "I am looking for code" I'll assume you're dealing with fill colours, not font colours.

You need to describe what you're trying to do, then we might be able to help.

Rgds,
Andy
 

flipinoluv

Thread Starter
Joined
Apr 16, 2004
Messages
21
Ha, sorry about the confusion.
I am looking for fill colors, although font colors might not be a good thing to know as well.
I am trying to create a drop down list for every cell in a worksheet. I plan on having the user select text from a list, and then have the conditional formatting to change font color as well as the fill color to be the same color, essentially making the box appear as one color.
Unfortunately, excel only allows me have 3 conditional formats and I need a minimum of 4.
Thanks.
 
Joined
Aug 30, 2003
Messages
2,702
OK. MustBNuts says Select Case is best for this, but since I haven't figured that out yet ...

Let's say your validation (list) options are Red, Blue, Green, Yellow.

Rightclick the sheet tab and View Code. Paste this in -

--------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "Red" Then
Selection.Interior.ColorIndex = 3
Selection.Font.ColorIndex = 3
ElseIf Target = "Blue" Then
Selection.Interior.ColorIndex = 5
Selection.Font.ColorIndex = 5
ElseIf Target = "Green" Then
Selection.Interior.ColorIndex = 10
Selection.Font.ColorIndex = 10
ElseIf Target = "Yellow" Then
Selection.Interior.ColorIndex = 6
Selection.Font.ColorIndex = 6
ElseIf Target = "" Then
Selection.Interior.ColorIndex = 0
Selection.Font.ColorIndex = 0
End If
End Sub
--------------------------------------------------------------

Close the Visual Basic Editor, then test the validation.

Rgds,
Andy
 

flipinoluv

Thread Starter
Joined
Apr 16, 2004
Messages
21
OK. Here is the issue.
I need the current cell to change the colors.

What is happening is that after I type in, "Red" it turns the next cell i select to Red. I am not sure why this is happening.
 
Joined
Aug 30, 2003
Messages
2,702
>> after I type in, "Red" it turns the next cell i select to Red

Yeah, that's your fault (for saying "I plan on having the user select text from a list" in the first place). :p ( ;))

Change your statements to

If Target = "Red" Then
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 3

Rgds,
Andy
 
Joined
Aug 21, 2003
Messages
2,016
In the Select Case (used in vba) it's really easy, you are simply replacing your IF with a Case and encapsulating it with a variable to look at and the end select:

'anything after a single quote mark is a comment that will not interfere with code
Select Case Target

Case "Red"
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 3

Case "Blue"
Target.Interior.ColorIndex = 5
Target.Font.ColorIndex = 5

Case Else
msgbox "Your choice is not available", vbokonly

end select


This is the structure, and you can add as many "cases" as you have situations and add as many instruction code lines in those cases as you need. Try to ensure that you always have a Case Else just for the oddball user who defies logic! :D

It is just a bit easier than an If Then Else because you do away with the elseif scenario and deal only with the if (case).

Hope this helps,

MBN
 
Joined
Aug 30, 2003
Messages
2,702
>> It is just a bit easier than an If Then Else

Yes. Works just the same as, but is infinitely more followable. Exactly as you said before ; if only my skull wasn't impenetrable.

This is an excellent example ; I'm stealing it.

Thanks,
Andy
 
Joined
Aug 21, 2003
Messages
2,016
Tsk, tsk, tsk....how quickly they forget....and I thought I'd taught you better than that....


Where is your Case Else?

...and BTW, that's what kids and pets are for (so they say), they eat you out of house and home, use up all your resources and claim that the payback is emotional fulfillment! ...but at least you can put the dog in a kennel when you want to go on vacation! :D:D

MBN
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top