ExcelBanter - Formula help for complex formatting.

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Formula help for complex formatting. (https://www.excelbanter.com/excel-worksheet-functions/52826-formula-help-complex-formatting.html)

Formula help for complex formatting.

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.

--
Thank you,

Formula help for complex formatting.

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:
=$M10 (select Cell shading - No color),
click Add-, and in condition 2 put
"Formula Is:" and put formula:
=$J10 Cell Shading - Red),
click Add-, and in condition 3 put
"Formula Is:" and put formula:
=$A10 Cell Shading - Yellow)

"SCrowley" wrote in message
...
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.

--
Thank you,

Formula help for complex formatting.

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

"Mladen_Dj" wrote:

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:
=$M10 (select Cell shading - No color),
click Add-, and in condition 2 put
"Formula Is:" and put formula:
=$J10 Cell Shading - Red),
click Add-, and in condition 3 put
"Formula Is:" and put formula:
=$A10 Cell Shading - Yellow)

"SCrowley" wrote in message
...
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.

--
Thank you,

Formula help for complex formatting.

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.....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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TKT" wrote in message
...
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

Formula help for complex formatting.

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

"Max" wrote:

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.....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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TKT" wrote in message
...
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

Formula help for complex formatting.

You're welcome, TKT !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TKT" wrote in message
...
Thank you so much Max! This website is awesome!!! Microsoft MVPs/ expert
users ROCK!! :) ~TKT