If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting...Please Advise
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 |
#2
|
|||
|
|||
Conditional Formatting...Please Advise
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. "Dermot" wrote: 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 |
#3
|
|||
|
|||
Conditional Formatting...Please Advise
Select cells B6:M6
Format - Conditional Formatting Formula equals =$K$6="YES" Set the format you want. "Dermot" wrote in message ... 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 |
#4
|
|||
|
|||
Conditional Formatting...Please Advise
Select cells B6:M6
Choose FormatConditional 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 wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Conditional Formatting...Please Advise
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 "Debra Dalgleish" wrote: Select cells B6:M6 Choose FormatConditional 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 wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
Conditional Formatting...Please Advise
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
|
|||
|
|||
Conditional Formatting...Please Advise
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" wrote: 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
|
|||
|
|||
Conditional Formatting...Please Advise
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 ? Select B6:M20 (with B6 active) 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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dermot" wrote in message ... 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 |
#9
|
|||
|
|||
Conditional Formatting...Please Advise
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 "Max" wrote: 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 ? Select B6:M20 (with B6 active) 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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dermot" wrote in message ... 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 |
#10
|
|||
|
|||
Conditional Formatting...Please Advise
Oh, thanks for the download that was good cheers.
"Dermot" wrote: 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 "Max" wrote: 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 ? Select B6:M20 (with B6 active) 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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dermot" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting in form slows down calculations | Susan | Using Forms | 5 | June 13th, 2005 11:24 AM |
Conditional Formatting Error | ddate | Worksheet Functions | 0 | May 5th, 2005 09:00 PM |
conditional formatting conflict? | Abi | Worksheet Functions | 2 | January 11th, 2005 03:41 PM |
Conditional formatting over a picture | Carl Rapson | Setting Up & Running Reports | 0 | December 27th, 2004 04:21 PM |
Copy Conditional Formatting | Bianca | Worksheet Functions | 2 | August 9th, 2004 01:47 PM |