colour code a drop down menu with only two options

[flylikeanangel]

hi. i have a snap shot of my drop down cell issue but i do not think it can be uploaded.

so it goes as follows.

cell d40 is a drop down with just two options over and under

cell h40 is auto formulated cell either showing positive number or a negative number.

i have been ask to make d40 turn red when it is a negative .. showing the word under
and
d40 to show white if the number is positive.

i have tried a few unconditional formats .. but no luck

can you help me with this .. thanks barbara

 

[flylikeanangel]

no luck. d40 is the drop down two options over and under .. do i need to make the first drop down option a blank, next over and next under.?
cells G37-G39 are merged cells with the total of a positive or negative number showing in G40. right now that is -$53.99.
so if i take out the drop down and the managers just type the words over .. it can appear white. and it the type the word under it can appear as red font or red box with white font.
as you stated .. there is not connection between the d40 and h40.

when i go to unconditional formatting .. ask for new rule, i get two colour, three colour, icon, data, and classic ? the classic has about 6 rules but none of them seem relevant to what i would like.

... any other suggestions? thank you.

 

[FDibbins]

I cannot change your post, and I don't think you can now either, I have asked a mod to remove it

I explained how to do what you want, in post #8

That can be done with Conditional Formatting
=H40<0 format fill RED
You only need 1 rule for this, just leave the cell white, and let CF change it when neg

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula H40<0 format fill RED

 

[flylikeanangel]

I cannot change your post, and I don't think you can now either, I have asked a mod to remove it

I explained how to do what you want, in post #8



1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula H40<0 format fill RED

" i give up. i will move to another forum to see if i can get my issue resolved. thx." of note, you can explain over and over but if you do not answer my questions (like should i remove the drop down and manually input over and under) nothing gets resolved. sorry to have wasted your time worrying about my email.

 

[FDibbins]

Your initial question was how to make a cell change color, based on he contents of another cell - and my responses have been directed towards helping with that.

Just to explain - a DropDown is essentially the exact same as entering data manually. All it really does, is save typing, avoid typos, and keep entries uniform. So no, it would probably be better to keep the DD there, at least then you know you will get the entries you want.

(a little patience goes a long way in all of these forums. Keep in mind that the way you (or I) write something, is not necessarily the way that other people might read it - 1 of the bigger drawbacks of written communication)

 

[flylikeanangel]

Your initial question was how to make a cell change color, based on he contents of another cell - and my responses have been directed towards helping with that.

Just to explain - a DropDown is essentially the exact same as entering data manually. All it really does, is save typing, avoid typos, and keep entries uniform. So no, it would probably be better to keep the DD there, at least then you know you will get the entries you want.

(a little patience goes a long way in all of these forums. Keep in mind that the way you (or I) write something, is not necessarily the way that other people might read it - 1 of the bigger drawbacks of written communication)

"yes, agreed"

3. select new rule, select use formula H40<0 format fill RED from here, i just get new rule .. and it takes me to several options and not as simple as you state in #3. or i think i would have my answer. new rule brings me to a drop down menu of 1. two colour with options, 2. three colour with options, 3. data, 4. icon and #5. Classic. ??

 

[FDibbins]

What excel version are you using? (if you mentioned it, I missed it)

 

[flylikeanangel]

What excel version are you using? (if you mentioned it, I missed it)

" i am on a mac excel 2011 however the store is using pc excel 2016 "

 

[FDibbins]

You need to look at the you of that window, where it has a bunch of different rules (I think you are looking at the "edit the rule description" part lower down in that window)

Don't use (or know mac), but from 2007 onwards, when you go into CF and select New Rule, you should have a window open up that gives you these options...
1. Format cells based on their value
2. Format only cells that contain
3. Format only Top or Bottom ranked values
4. Format only values that are above or below average
5. Format only unique or duplicate values
6. Use formula to determine which cells to format

6 is the rule you need to select

 

[flylikeanangel]

.. fyi .. issue resolved at another forum. thanks for trying. an = if was needed prior to the CF.