Conditional formatting based on another cell

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

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

Cheers Steve,

Works a treat mate.

"Shaggyjh" wrote:

> 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
>

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



"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=510640
>
>

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

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


"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=510640
>
>

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

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

James


"SteveG" wrote:

>
> 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
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=510640
>
>