Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting...Please Advise
You're welcome, Dermot.
Glad it helped and you finally got it working right ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dermot" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional formatting : amount of decimals | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |