QUES: Excel conditional formatting help needed

[ngp]

I am using Excel 2010 with Windows 7

I want to setup a conditional formatting where a particular field highlights based on certain input in a another field.

For example, if field D16 has any one of the below values, I'd like to highlight field D17 in yellow; and keep it yellow, even if the user inputs something into D17:

Decommissioned
Retired
Retired & Transferred
Retired (Renamed)
Retired To Inventory

Any thoughts on a possible formula to do this?

Thanks in advance for any help!

[bluetoast]

For a second I thought SUMIF would work, but it would require numbers at some point. I think this might help for the highlighting, but I'm not sure how to get the initial conditions set up.

http://www.exceltip.com/st/Coloring_Rows_Based_on_Text_Criteria/294.html

[danwiz]

I played with it for about an hour, but sorry, couldn't come up with anything.

[Raul3]

I just googled "excel conditional formatting", I don't know if that's really what you are looking for though.


http://www.contextures.com/xlCondFormat02.html

[dleedlee]

I'm a bit rusty but I tested this using OpenOffice, Excel should be very similar (Excel uses comma instead of semicolon below)

Set up a table in, A1:B6, with values:

Decommissioned 1
Retired 1
Retired & Transferred 1
Retired (Renamed) 1
Retired To Inventory 1

In E16 (hide, or anywhere else): =VLOOKUP(D16;A$1:B$6;2;1)
In D17, under Conditional Formatting, Formula Is: IF(E16=1)

Vlookup works better with discrete fixed values. Do you have to worry about typos? If so then modify above to something like table with values:

1 Decommissioned
2 Retired
3 Retired & Transferred
4 Retired (Renamed)
5 Retired To Inventory
6 - error (typo condition)

Keep formula in E16 same to display value (or adjust and shift columns over if text has to be in D16)
In D17, under Conditional Formatting, Formula Is: IF(E16<=5) (or shift columns)

[ngp]

Thanks all for the suggestions below...I'll look through each.
DleeDlee...I'll give this a try, that seems to be along the lines of what I want to accomplish.

Thanks again!!!

[starman9000]

Just go to conditional formatting, new rule, use a formula to determine which cells to format, and do this:

=OR(D16="Decommissioned",D16="Retired",D16="Retired & Transferred") (etc...)

[ngp]

Just go to conditional formatting, new rule, use a formula to determine which cells to format, and do this:

=OR(D16="Decommissioned",D16="Retired",D16="Retired & Transferred") (etc...)

When I use the below formula

=OR(D16="Decommissioned",D16="Retired",d16=”Retired To Inventory”)

I get an error: "You may not use reference operators (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria."

I didn't mention in my original post but it's worth noting that the values in D16 are a picklist. I thought that the error above may have to do with that, however, I tried it on other cells and updating the formula for the general cell and still get the error.

[starman9000]

Oh sorry, I guess you might not be able to do that in 2010 (I tried it on 07 and it worked). Does it work with just a single criteria? (=D16="Decommissioned") If so, you could just make one for each.

[ngp]

Oh sorry, I guess you might not be able to do that in 2010 (I tried it on 07 and it worked). Does it work with just a single criteria? (=D16="Decommissioned") If so, you could just make one for each.

Must confess....my stupid mistake. In the last criteria, the quotation mark was the wrong type. Once I corrected that, it worked! Thanks!

[starman9000]

Oh good, I thought that would be a weird thing for them to change.