Conditional Formatting

[Kate1]

It's been a while since I have posted, so not sure if I am posting correctly this time.

I have an issue with conditional formatting in cells D6:D14. In Column D a response is returned depending on what is input in C13. The effect I am trying to achieve is where there is no value in C13, then there is no message returned in D13, and the cell is white and not orange/red front. When there is a value in C13, and it meets a condition then a message is returned in red/orange.

The current formula in D13 is:

=IF($C13="","",IF($C13>$D$3,"$ > $1.5M, notify CEO.",IF($C13>$D$2,"$ > $750k, notify General Manager",IF($C13>$D$1,"$ > $200k, notify Program Manager","")))) and returns the relevant message where there is a corresponding input in C13.

The current conditional formatting that applies to D13 is:
=AND(OR($C6>$D$3,$C6>$D$2,$C6>$D$1)) (Format orange and red font)as well as
=AND($C6<$D$1) (No Format)

I think there is something not quite right with A

ND(OR($C6>$D$3,$C6>$D$2,$C6>$D$1)) ​

<strike></strike>


I have tried to paste in the image below.

[TABLE="width: 492"]
<tbody>[TR]
[TD="colspan: 3"]Hidden Value >[/TD]
[TD]$200,000[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Hidden Value >[/TD]
[TD]$750,000[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Hidden Value >[/TD]
[TD]$1,500,000[/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD]Title[/TD]
[TD]Amount[/TD]
[TD]Action required[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Original Price >>[/TD]
[TD="align: right"]$2,000,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Title 1[/TD]
[TD="align: right"]$50,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Title 2[/TD]
[TD="align: right"]$210,000.00[/TD]
[TD]$ > $200k, notify Program Manager[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Title 3[/TD]
[TD="align: right"]$775,000.00[/TD]
[TD]$ > $750k, notify General Manager[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Title 4[/TD]
[TD="align: right"]$50,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Title 5[/TD]
[TD="align: right"]$130,000.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Title 6[/TD]
[TD="align: right"]$250,000.00[/TD]
[TD]$ > $200k, notify Program Manager[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Title 7[/TD]
[TD="align: right"]$1,750,000.00[/TD]
[TD]$ > $1.5M, notify CEO.[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Title 8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Title 9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[steve the fish]

Why not just use the result of your formulas:

cell value equals "$ > $1.5M, notify CEO."

etc.

 

[Kate1]

Hi, I have tried that but can't seem to get it to work properly. All of the cells go white which is not what I need. I've taken out the range reference so it only applies to one cell. So, not sure what else to try. Thanks.

 

[Fluff]

If you simply want to highlight a cell in D6:D14 if it has a value try
=D6<>""

 

[steve the fish]

Ok follow this. Press Home. Conditional Formatting. New Rule. Format only cells that contain. Change 'between' to 'equal to'. Put in the box '$ > $200k, notify Program Manager' without the quotes. Choose your format. Press ok. Repeat for '$ > $750k, notify General Manager' and '$ > $1.5M, notify CEO.'. Press conditional formatting again. This time manage rules. Applies to is D6:D14.

 

[Kate1]

Thanks for that. I have used.

 

[Kate1]

Thanks Steve. I got it working now. Tganks so much. Karen.