Complicated Conditional Formatting

[sarahsarah]

I have data that I need to format (a la a Gantt chart).

The data is:

-Project Name
-Project Health (3=red, 2=yellow, 1=green)
-Durations of project

My formula to code the Duration of the project works and is this: =AND(P$3>=$J4, O$3<=$M4).

However, I want to amend this formula so I can shade the Durations different colors based on their Project Health.... something along the lines of IF project health = 3, and =AND(P$3>=$J4, O$3<=$M4), then Color will be RED.

I cannot figure out how to amend the formula to do this... anyone have any ideas?

Thanks!

 

[jbodell]

At a glance, it looks like the solution would be:

=if(and(project health=3, P$3>=$J4, O$3<=$M4)

This checks the three parameters: (1) project health=3; (2) P$3>=$J4 and (3) O$3<=$M4). If all three are true, your conditional formatting will be applied.

 

[sarahsarah]

Thanks for that... I tried the formula, like below, but it doesn't seem to work...

=IF(AND($G4=3, P$3>=$J4, O$3<=$M4))

[$G4 is the Health Column and 3 denotes RED]

 

[Special-K99]

Conditional Formatting doesn't need an IF
You need to state just the conditions when the condition(s) is/are true and the format should be applied

Try

=(AND($G4=3, P$3>=$J4, O$3<=$M4))

 

[sarahsarah]

Thank you! That works