Excel:Additional Conditional Formatting

[flipinoluv]

I notice that you are limited to only 3 conditional formats. Is there a way to add more?

Thanks

 

[XL Guru]

There's a workround for 6 font colours. For >3 fill colours you have to use code. Which are you after?

Rgds,
Andy

 

[flipinoluv]

Yes, that is correct. I am looking for code that would include formatting for a minimum of 4 colors. Thanks.

 

[XL Guru]

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]

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.

 

[XL Guru]

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]

Thanks, I think I can work with this. I appreciate your help.

 

[XL Guru]

You're welcome.

Rgds,
Andy

 

[flipinoluv]

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.

 

[XL Guru]

>> 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). ( )

Change your statements to

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

Rgds,
Andy

 

[MustBNuts]

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!

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

 

[XL Guru]

>> 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

 

[MustBNuts]

Where would you like me to send the bill?!

 

[XL Guru]

You choose.

Case 1
"Bill Gate$"

Case 2
"My eldest (owes me a fortune.)"

 

[MustBNuts]

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!

MBN