how to colour code a drop down menu

[loonie]

hello. i need to colour code a drop down menu with just two options over and under

so when a amount in a formulated cell gives a number number i would like the word Under to be in Red and the Over can just stay as black.

I do not really need the cell to be red in colour .. just the text.

for example cell d40 is the drop down cell h40 is the cell where either a postiive or negative number will appear pending the inputted bank deposit of the day.

i can attach a print screen of the few cells if need be.

thank you.

of note, if i do not need to have a drop down .. i can get the managers to just type over or under pertaining to the value to the right.

[Tony Valko]

Not real clear.

So, you have a drop down list with 2 options, Over or Under?

When Under is selected you want the text to turn red?

What version of Excel are you using?

[loonie]

hello tony. thanks for the speedy response.

the drop down is in cell D40 with to options .. over and under .. however that is not changed until the bank deposit
is put into cell h40. so therefore, if h40 is a negative number and the manager select the under from the drop down
it will be Red. if a positive number the over stays white.

mac excel 2011

thanks a bunch.

[Tony Valko]

I'm not familiar with Mac Excel 2011.

If I understand what you want to do here's how I would do it in Windows Excel 2010.

Select cell D40

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=AND(D40="Under",H40<0)

Click the Format button
Select the Font tab
From the Color drop down select a shade of red
OK out

[loonie]

hi thanks for the mgs.

no luck.

when i put in the unconditional format in d40 that you gave me above. the under does not turn red.

cell h40 does show a negative number .. as the bank deposit was not enough to cover the cash for the day.

so even when i play around with the cell values to increase the negative in h40 .. i would hope to toggle the drop
drop and the under would appear red.

can i send you a snap shot of the cells involved.

regards, barbara

[Tony Valko]

Let me see if I can find someone that also uses a Mac...

[loonie]

tony .. i do not think it is a mac issues. i see all the selections you guided me to .. it appears under Classic in the new rule section.

it just not seem to like the formatting .. ?? i will leave it with you if you think a mac associate could help.

thanks again

[xlnitwit]

Hi,

Can you clarify which cell should change its font colour- D40 or H40?

[loonie]

hello. thx for the response. the drop down cell is D40 .. just two options over or under

h40 is the sum of h39 - h37 which gives a negative number .. therefore since negative when the manager's tab to the next cell to complet d40 they pick over or under. under to be in red.

thx.

[xlnitwit]

So D40 should be red, not H40? Do you mean you want "Under" to appear in red in the cell, or in the dropdown list? The latter is not possible but Tony's suggestion will accomplish the former in 2011.

[peterrc]

Hi,

Is the attached what you are looking for?
With one difference - D40 is automatic - H40 negative "under" in red - H40 positive "over" in black.

Regards

peterrc

[peterrc]

Hi,

Is the attached what you are looking for?
With one difference - D40 is automatic - H40 negative "under" in red - H40 positive "over" in black.

Regards

peterrc

[loonie]

we are getting close. can i attach my example .. i tried you rule but the under does not turn red ... i am getting confused .. LOL.

[JohnTopley]

What is wrong with Tony's solution:

D40 with CF rule:

=AND(D40="Under",H40<0)

will turn the word "Under" red if H40 is < 0 OR set the background to RED depending whether you use Font or Fill

[loonie]

the under does not turn Red.

i was told to leave as a drop down. it can be sitting on over or under .. depending where the manager leaves it last

when i put the CD in D40 .. the under does not turn red.

it does when i change Tony's number in his exammple. so i am wondering if i should just take out the drop down menu

thx.

[Tony Valko]

Can you post a SMALL sample file so we can see what's happening?

From your description of what you want to do this should be fairly easy to implement and should not be causing this much aggravation.

[Tony Valko]

Here's a sample file I put together.

When cell H40 is less than 0 AND Under is selected from the drop down list in cell D40 the text Under is colored red.

Is that not what you want?

[loonie]

example cash sheet.xlsx

i am ok with this sheet .. i have taken out reference to the company, etc. the numbers are just random numbers for testing. thanks.

[Tony Valko]

Ok, I downloaded your file and now I see what you mean.

If D40 already contains Under and the calculation changes the cell value in H40 the format of cell D40 doesn't change unless/until you select the cell.

This appears to be a glitch in the data validation application.

It looks like you could use a formula to populate the Over/Under and have it done automatically rather than needing to be manually selected.

Do you think that is a viable option?

[Tony Valko]

P.S.

This works as desired in my sample file.

I'm kind of confused as to why it doesn't work in your file.

[loonie]

omg. thank you. i keep getting people to tell me how and i tried, tried and tried .. and i could not get it right.

so, your suggestion is to take out the drop down menu and just have a manager go to the cell and type over or under.

if that is the case, i still want the word under to appear in red.

thanks for looking into this. your patience is much appreciated.

[Tony Valko]

so, your suggestion is to take out the drop down menu and just have a manager go to the cell and type over or under.

No, replace the drop down list with a formula.

It looks like if cell H40 is less than 0 then you want cell D40 to display Under otherwise, display Over.

This could be done with a simple formula and then the conditional formatting will probably work.

[JohnTopley]

In D40

=IF(G40="","",IF(G40<0,"Under","Over"))

Set CF

=$D$40="Under"

Click Format==>Font==>Colour ==>OK


See attached.

NOTE: Value is G40 not H40 (merged cells ... the cause of many problems!!))

[Tony Valko]

In D40

=IF(G40="","",IF(G40<0,"Under","Over"))

I think we need some clarification from the OP.

If the value is 0 that is neither under nor over.

[loonie]

Hello Tony and John. I think it is all ok now. i check the sheet uploaded and that is exactly what i was needing. very much appreciate all your time and effort to
get this working. have a great day. regards, Barbara

[Tony Valko]

You're welcome. Thanks for the feedback!