if statement to shade cells (more than 3 conditions)

[Mo2]

If A4= 2, shade E4 blue
If A4= 3, shade E4 yellow
If A4=4, shade E4 brown
If A4=5, shade E4 orange
If A4=6, shade E4 purple

can't use conditional formatting (there is more than 3 conditions)

can anyone help?

P.S. i know nothing about visual basic

 

[Teethless mama]

XL2007 you can get as many conditions as you want as long your system can
handle.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("E4")
If Not Intersect(Target.Cells, .Cells) Then
Select Case .Value
Case 2
.Interior.ColorIndex = 5
Case 3
.Interior.ColorIndex = 6
Case 4
.Interior.ColorIndex = 4
Case 5
.Interior.ColorIndex = 53
Case 6
.Interior.ColorIndex = 13
Case Else
.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End WIth
End Sub

 

[Marco]

Hi JE. I also have a problem like this. I need this:
http://www.microsoft.com/communitie...misc&mid=930006d8-ad58-4f97-85ab-9966cf95be93

How can I control that?

Regards,
Marco

 

[JE McGimpsey]

Hi JE. I also have a problem like this. I need this:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsof
t.public.excel.misc&mid=930006d8-ad58-4f97-85ab-9966cf95be93

How can I control that?

No idea - what you're trying to accomplish is not apparent to me from
that thread.

Perhaps more detail about what conditions and results you're looking for
would help.

 

[Lola]

Hi:
First, thank you for your posting, they are very helpful.
I am having kind of the same problem than Mo2 with the difference that the
cells that need to shade are in different table, but within the same
worksheet.
This is an example of what I am trying to do:

If cell value is >= -0.1 but <= -0.150 then color the cell in green
I do have formulas that will calculate the different values, lets say:
A1-B1 = -0.125

Please note: I only want to create a macro that change color once the
results are in. I do notwant to include the formulas. I created a botton to
have the macro assign to it.

Thank you so much for any suggestions you might have

 

[JE McGimpsey]

I don't really understand what you're trying to do...

For one thing, you *can't* have a number that is ">= -0.1 but <= -0.150".
If a number is < -0.15 then it is by definition < -0.10

I'm also not sure what you meant by "once the results are in" - are you
looking for an automatic (event-based) macro, or one that you invoke
manually?

 

[Phill]

G'day JE,
You seem to be the guy that could probably help me out no end here

I want to use the Data Validation drop down list function.
I know how to do this
I ultimately want to select from the drop down list, and have that selection
appear in the cell, but of a different font and cell colour.
I have say 12 items in my data validation list
I have looked at the conditional formatting, which gives me 3 options only,
but even there I seem to have a problem in trying to use the three options to
cover the 12 items, (say items 1-4 could be one colour but I have a problem
in finding the right formula for stipulating items 1-4 in option 1. Any
ideas on this one at all?
I do not have any ideas on VBA, but I see you you may be able to generate
some code for this....unfortunately I will need very clear and concise
instructions...(doh!)

 

[JE McGimpsey]

One way using CF:

CF1: Formula is =OR(A1="value1",A1="value2",A1="value3",A1="value4")
Fmt1: <font>/<color1>

CF2: Formula is =OR(A1="value5",A1="value6",A1="value7",A1="value8")
Fmt2: <font>/<color2>

etc.