Conditional Formatting using IF and AND

[keepleft99]

Hello,

I have a spreadsheet like this excel stuff.jpg and what i am trying to do is to create some conditional formatting where it will find the cell with "Break" in it and then change the colour of the cell with the time in it. so if its over 10min red, under 10min green.

I have tried to use

=AND($A$6:$A$30="Break", $D$6:$D$30<=00:10:00)

but that didnt work. Any suggestions?

Thanks

Andy

[Naveed Raza]

In conditional formatting reference cell use below function

=AND($A6="Break", $D6<=00:10:00)

and in Apply to reference select the range till where u want (Conditional Formating -> Manage Rule -> Select ur C.Formating -> Apply to field)

[sktneer]

Select D6:D30 and make a New Rule for conditional formatting using the formulas below and set the format as per your choice.

For Red:

Please Login or Register  to view this content.

For Green

Please Login or Register  to view this content.