Formula help for complex formatting.

[Guest]

I am trying to get Conditional formatting to achieve a result to no avail.

Here is what I'd like it to do:

Column A - if cell, in Row is >0 then shade active Row (ColumnA:ColumnM)YELLOW
THEN if cell in Column J is >0 then shade activated Row (ColumnA:ColumnM)
RED (this supersedes previous format of Yellow)
THEN if cell in Column M is >0 the remove all shading from active Row.

Column A logs in that something was received, Column J means the received
item was rejected and must be re-submitted, Column M is the close out showing
final has been approved.

Can this be written as a formula or does this need VBA?

Thank you.

 

[Mladen_Dj]

Use all three conditions for your range (A:M), but in opposite direction you
write them here. It means in conditional formatting dialog for condition 1
select:
"Formula Is:" and put formula:
=$M1>0 (select Cell shading - No color),
click Add->, and in condition 2 put
"Formula Is:" and put formula:
=$J1>0 Cell Shading - Red),
click Add->, and in condition 3 put
"Formula Is:" and put formula:
=$A1>0 Cell Shading - Yellow)

 

[Guest]

I can't belive I didn't think of that. It's Friday and I'm going home!!!
THANK YOU THANK YOU THANK YOU
--
Thank you,
(e-mail address removed)

Use all three conditions for your range (A:M), but in opposite direction you
write them here. It means in conditional formatting dialog for condition 1
select:
"Formula Is:" and put formula:
=$M1>0 (select Cell shading - No color),
click Add->, and in condition 2 put
"Formula Is:" and put formula:
=$J1>0 Cell Shading - Red),
click Add->, and in condition 3 put
"Formula Is:" and put formula:
=$A1>0 Cell Shading - Yellow)

 

[Guest]

Hi There,

I have the following question:

What if I have a drop down list....can I still set the cell/ column to
recognize the various color codes set? Example given below:

F = Fixed (want to reflect blue)
R = Rendering (want to reflect green)
N = Null (want to reflect yellow)
P = Pass (want to reflect grey)

My drop down menu has the options "F", "R", "N" and "P"....once someone
selects "F" or one of the other 3, can it be assigned the various colors
which I've listed above? I am desperately trying to have this automatically
color the cell once the code is selected.

Please advise....thank you!
~TKT

Use all three conditions for your range (A:M), but in opposite direction you
write them here. It means in conditional formatting dialog for condition 1
select:
"Formula Is:" and put formula:
=$M1>0 (select Cell shading - No color),
click Add->, and in condition 2 put
"Formula Is:" and put formula:
=$J1>0 Cell Shading - Red),
click Add->, and in condition 3 put
"Formula Is:" and put formula:
=$A1>0 Cell Shading - Yellow)

 

[Max]

The CF will work on the selections made from data validation droplists. But
your requirement unfortunately exceeds the normal CF limit of 3 conditions
(excluding the default) by one. If you're willing to accept an approx
visual using normal CF, say, accept a default fill color of grey, then you
could select the column with the DVs, preformat the entire col with grey
fillcolor, and set the 3 CF conditions:

Cond1: Cell value: equal to: ="F" (Blue)
Cond2: Cell value: equal to: ="R" (Green)
Cond3: Cell value: equal to: ="N" (Yellow)

Demo file at: http://cjoint.com/?kDdlLmNjpz

To get more CF colors than the top 3, you could check out:

a. Bob Phillips' CFPlus - Extended Conditional Formatter add-in at:
http://www.xldynamic.com/source/xld.CFPlus.Download.html
(Bob's write-up there states that up to *30* cond formats are catered for)

b. JE McGimpsey's page at:
http://www.mcgimpsey.com/excel/conditional6.html
[Getting 6 conditional font colors without macros]
The page also contains links to Dave McRitchie's & Chip Pearson's CF pages

 

[Guest]

Thank you so much Max! This website is awesome!!! Microsoft MVPs/ expert
users ROCK!! ~TKT

The CF will work on the selections made from data validation droplists. But
your requirement unfortunately exceeds the normal CF limit of 3 conditions
(excluding the default) by one. If you're willing to accept an approx
visual using normal CF, say, accept a default fill color of grey, then you
could select the column with the DVs, preformat the entire col with grey
fillcolor, and set the 3 CF conditions:

Cond1: Cell value: equal to: ="F" (Blue)
Cond2: Cell value: equal to: ="R" (Green)
Cond3: Cell value: equal to: ="N" (Yellow)

Demo file at: http://cjoint.com/?kDdlLmNjpz

To get more CF colors than the top 3, you could check out:

a. Bob Phillips' CFPlus - Extended Conditional Formatter add-in at:
http://www.xldynamic.com/source/xld.CFPlus.Download.html
(Bob's write-up there states that up to *30* cond formats are catered for)

b. JE McGimpsey's page at:
http://www.mcgimpsey.com/excel/conditional6.html
[Getting 6 conditional font colors without macros]
The page also contains links to Dave McRitchie's & Chip Pearson's CF pages


--

Hi There,

I have the following question:

What if I have a drop down list....can I still set the cell/ column to
recognize the various color codes set? Example given below:

F = Fixed (want to reflect blue)
R = Rendering (want to reflect green)
N = Null (want to reflect yellow)
P = Pass (want to reflect grey)

My drop down menu has the options "F", "R", "N" and "P"....once someone
selects "F" or one of the other 3, can it be assigned the various colors
which I've listed above? I am desperately trying to have this automatically
color the cell once the code is selected.

Please advise....thank you!
~TKT

 

[Max]

You're welcome, TKT !