 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2005Conditional 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 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. > If I have a row of cells B6:M6 > A cell K6 which is "List" validated Yes, No [quoted text clipped - 8 lines] > Please advise the correct way for me to achieve this objective. > Thanks Select cells B6:M6 Format -> Conditional Formatting Formula equals =$K$6="YES" Set the format you want. > If I have a row of cells B6:M6 > A cell K6 which is "List" validated Yes, No [quoted text clipped - 8 lines] > Please advise the correct way for me to achieve this objective. > Thanks 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 > If I have a row of cells B6:M6 > A cell K6 which is "List" validated Yes, No [quoted text clipped - 8 lines] > Please advise the correct way for me to achieve this objective. > Thanks  SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html 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 > Select cells B6:M6 > Choose Format>Conditional Formatting [quoted text clipped - 15 lines] > > Please advise the correct way for me to achieve this objective. > > Thanks > 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 -- 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 > > How do I extend this formula so ................ > > 1. No = Red [quoted text clipped - 30 lines] > http://savefile.com/projects/236895 > -- > 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 -- > Hi Max, > You resolve one problem for me....I hadn't worked out how to apply two [quoted text clipped - 22 lines] > Thanks > 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 > > What I want to do is........ > > When I select Yes (or No) in any cell in column K, for example K6, I would [quoted text clipped - 59 lines] > > Thanks > > Dermot Oh, thanks for the download that was good cheers. > Thank you again Max..... > [quoted text clipped - 68 lines] > > > Thanks > > > Dermot 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 -- > Oh, thanks for the download that was good cheers. > [quoted text clipped - 4 lines] > > > > Have a good new year |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.