Conditinal formating?

[RustyNail]

Hello,

If we click on a cell (Mine is Z9) and then you click on "conditional formatting" and then on "manage rules", we come to a "conditional formatting rules manager" window!

I would like Z9 to be conditionally formatted based on two dropdown cells located at M9 and N9, When M9 shows " ***", and N9 shows anything other than " ***", I would like the
Z9 cell to show red. Anything other than this condition, I want Z9 to show white.

Heres the two rules I put in:

=AND(N9=" ***", N9=" ***")
>>> show white


and

=AND(M9=" ***", N9<>" ***")
>>> show red

Somehow this is not working for me.

Can anyone help me!

Thanks in advance!

[etaf]

=AND(M9=" ***", N9<>" ***")
>>> show red

that should work -

White is the default - so you should not need to format
also
this formula is incorrect
=AND(N9=" ***", N9=" ***")

can you upload a sample sheet as your red should work
Just try the RED formula

[RustyNail]

Hi Wayne,

I tried just the red and it doesn't work... sad!

r

[popipipo]

it doesn't work

What doesn't work?
What do you get?
What do you want.

Please upload a small excel workbook

[etaf]

see attached

you *** do have a space in front

[RustyNail]

Here's the file....

I would like Z9 to be conditionally formatted based on two dropdown cells located at M9 and N9, When M9 shows " ***", and N9 shows anything other than " ***", I would like the Z9 cell to show red. Anything other than this condition, I want Z9 to show white.

discouraged!

[etaf]

i'm not sure what is going on the formula is correct
and sometimes it works as i click around

tested in another cell just using the formula
=AND(M9=" ***", N9<>" ***")
and it changes true and false

though it maybe a calculation issue - but its not

seems to work if i click onto z9 and then click into the formula bar

[RustyNail]

That's weird ...

it doesn't work anywhere for me lol

thanks anyways

[etaf]

its not working correctly

click into cell Z9
then click on the fill and clear
then click into the formula bar at the ribbon

[RustyNail]

hi etaf,

I don't see "fill and clear" sorry!!!

But you don't even have to go that far.... Here... do a change on N9 (select ***) and click on the "defines" spreadsheet at the bottom and then
click back on the "CUR_EXP_DETAIL" spread sheet at the bottom and it works.... weird ... its like as if the work book needs a refresh of some sort!!!

[popipipo]

I think the file is corrupt because this file works

[etaf]

Yes, I thought it was not calculating - but its OK
But i dont know how to fix

[RustyNail]

If its corrupted, exporting it to a new workbook, would that solve the issue?

[RustyNail]

Okay, I created a new sheet, and copied the defective one over and now it works in the new sheet!

I would like that along when Z9 turns RED It can also show the word "PENDING"....

I am trying to do this but not that easy!!!! LOL

anycase thanks all for your help

[RustyNail]

In anycase, at Z9 I also added the formula:

=IF(AND(M9=" ***",N9<>" ***"),"PENDING","")

And I got the pending issue resolved as well....

all ends well

thanks

[etaf]

excellent , glad it all worked out ok
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here