Conditional formatting based on another cell

S

Shaggyjh

Hi there,

Basically i want cell F6 to change colour if cell F5 reads "Failed". I
can't seem to get the formula right!

I don't know if it makes a difference but in cell F5 i have used the
Validation function with a list of 2 options: Failed and successfull.

Any help would be greatly received, cheers

James
 
S

SteveG

James,

In F6 the CF should be.

Formula is: =F5="Failed"

Click on format to apply the fill you want. Click OK, OK. It should
not matter that F5 contains a Data Validation list.

HTH

Steve
 
S

Shaggyjh

Next problem is that i would like the formatting to disappear when a value is
entered into the F6 cell.

So i assume i have to enter a Condition 2, and that it will have to be an
"And" formula to have the F5="Failed" in as well as a part that changes the
formatting when a value is entered into F6.

Does that make sense? Hopefully!

Cheers
James
 
S

SteveG

If you want the format to be different then you do need to add a second
condition. If your formatting is going to be the same if the value
appears then,

=OR(F5="Failed",F5=YourValue)



Does that help?

Steve
 
S

Shaggyjh

That doesn't help really.

Basically i have 'condition 1' changing the colour in cell F6 if cell E6 has
"Failed" in it. Which works fine. I then want cell F6 to change to a
different colour if i write anything in cell F6, regardless of whether E6
still says "Failed" or not.

Hope that makes more sense.

James
 
S

SteveG

James,

I think I got it now.

In F6 your 1st condition should be:

Formula is: =F6<>"" then format how you need

Condition 2

Formula is: =E6="Failed" then format how you need.

Conditional formatting looks at your conditions in order so if the
first condition is true it will apply that even if your other
conditions are met. By putting the evaluation of cell F6 first,
anytime it contains a value, it will turn to that format but if not it
steps down to condition 2.

Does that help?

Steve
 
S

Shaggyjh

Yeah that works fine. I actually tried it just before i got your post. Many
thanks for all your help Steve.

James
 
Top