Conditional Formating on value of cell and prev cell

[Phillips]

I all looking to create a conditional format based on the value of the
current cell (if cell contains string) AND if prev cell has a given time
value (between 9:00 AM and 12:00 PM) turn it green
If cell contains STRING2 and prev cell has a given time value (between 9:00
AM and 12:00 PM) turn it BLUE

If cell contains STRING3 and prev cell has a given time value (between 9:00
AM and 12:00 PM) turn it RED
If cell contains STRING2 and prev cell has a given time value (between 12:00
PM and 6:00 PM) turn it BLUE

If cell contains STRING2 and prev cell has a given time value (between 12:00
PM and 6:00 PM) turn it BLUE

and if it is outside of those times, then white

I know that this is possible, but I am not sure HOW to do it.
First, it is MORE than the 3 criteria and I also do not know how to do
formulas in the conditional formating.

Thanks
Phil

 

[Jason Morin]

I'll answer the first one to get you started. If you're
doing the conditional formatting for cell A2, then click
A2, Format > Conditional Formatting, Formula Is:

=AND(ISNUMBER(SEARCH("string",A2)),A1>=TIME(9,,),A1<=TIME
(12,,))

HTH
Jason
Atlanta, GA

 

[Steve]

Also you said that there are more than three conditions. No there
are not.

You have some cells when you do not want any formatting so they
have no conditional formatting changing them. Other cells you want
either RED, GREEN or BLUE. You will have to add the conditions
for the BLUE conditions. Jason has shown you how to do that. Use
what he did with the AND but add another OR to it.

= OR(AND(text test , time test) , AND(text test, time test))

That way you get BLUE for either of the two conditions.

If you need all the conditional format statements then just post again.

Steve.


Jason Morin wrote