Conditional Formatting...Please Advise

[Dermot]

If I have a row of cells B6:M6
A cell K6 which is "List" validated Yes, No

I would like the full range of cells B6:M6 to become Yellow, when the cell
is set to Yes from the list.

I have experimanted with the "Conditional Formatting" dialogue box, but
can't work out how to do this....I can only get one cell to change to
yellow....that being K6 if I select yes from the list.

Please advise the correct way for me to achieve this objective.
Thanks

 

[bpeltzer]

If you choose 'cell value is' in the first drop-down of the CF dialog, then
you can only adjust the current cell's condition. Instead, say in B6, use
'Formula is' and in the text box =$K$6="Yes". That should set the CF in B6
as you want. Then select the rest of your range and Edit > Repeat
Conditional Formatting.

 

[Barb Reinhardt]

Select cells B6:M6
Format -> Conditional Formatting
Formula equals =$K$6="YES"
Set the format you want.

 

[Debra Dalgleish]

Select cells B6:M6
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the text box, type: =$K6="Yes"
Click the Format button, and choose Yellow on the Patterns tab.
Click OK, click OK

 

[Dermot]

Thank you for the solution Debra.
This works fine for me.
Using this explanation I have tried unsucessfully to expand on it as
described below.
How do I extend this formula so ................
1. No = Red
2. All cells in column K behave in this manner?

Please advise
Thanks
Dermot

 

[Max]

How do I extend this formula so ................

1. No = Red
2. All cells in column K behave in this manner?

One way ..

Assume the range to be cond formatted is B6:M20

Select B6:M20 (with B6 active)

Click Format > Cond Formatting

Make the settings as

Cond1:
Formula is:=$K6="Yes"
Format: Yellow

Click "Add"

Cond2:
Formula is:=$K6="No"
Format: Red

OK out

 

[Dermot]

Hi Max,
You resolve one problem for me....I hadn't worked out how to apply two
conditional formats....using the add button.....I don't know how I missed
this...cheers.

Using a range like the one you suggested B6:M6, causes ALL the cells in the
range to change.....I only want the relevant row to change.....to be used as
a visual indication of the clients stage progress...if you know what I mean!

I didn't explain my objective properly Max.
Doing what you suggested conditionally formats all the cells in the range.

What I want to do is........
When I select Yes (or No) in any cell in column K, for example K6, I would
like the range of cells B6:M6 in that row only to change to the appropriate
colour.

Likewise K8 (Yes / No) would produce formatting on Row range B8:M8.

Do I have to format each row individually. What formula would I use for
this? I thought if I used relative references, I could copy down the
formula...but I don't seem to be able to get this to work.

Please advise.
Thanks
Dermot

 

[Max]

What I want to do is........

When I select Yes (or No) in any cell in column K, for example K6, I would
like the range of cells B6:M6 in that row only to change to the appropriate
colour.
Likewise K8 (Yes / No) would produce formatting on Row range B8:M8.

Yes, implemented correctly, that's exactly how it should have worked.

Did you select the range correctly as per the 1st step ?
The above means select by clicking on B6 first,
then dragging across/down to M20

The CF settings would then be correctly applied for each row
within the range B6:M20 at one go

Here's a sample construct to illustrate:
http://cjoint.com/?mDpCcXfbmG
Dermot_newusers.xls

(contains a screenshot of the selection & CF dialog settings as well)

 

[Dermot]

Thank you again Max.....

I don't know how I managed but I used two absolute references $K$6 instead of
=$K6="YES".

Have a good new year

Dermot

 

[Dermot]

Oh, thanks for the download that was good cheers.

 

[Max]

You're welcome, Dermot.
Glad it helped and you finally got it working right !