Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Question Conditional Formatting question - shading of one cell or groups of cells

    Excel Version 2010 - 14.0.6129.5000 32 bit - Windows 7 64 bit SP 1

    I am trying to do a conditional formatting of a very simple To Do list. Basically, I have a numbered list as follows -

    Column 1 - Task Number
    Column 2 - Task Description
    Column 3 - Task completed indicator

    What I want to happen is for the three columns to be shaded a light grey, when I put an indicator in column 3. Right now that indicator is the letter X. I also want to be able to shade it with a light orange when the task is partially completed as indicated by the letter P.

    I thought that I had this figured out using Conditional Formatting and the "formula" approach, but I have run into a brick wall and I cannot understand what is going on.

    I also want to know if it matters whether the letter, x or p, is typed in in upper case of lower case? If I type it in in lower case, how do I get it automatically translated to upper case so I do not have to worry about the <shift> key every time. Any inisght that anyone would care to provide will be greatly appreciated.

    Thanks in advance.

    Ron M

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    Is this what you are after?
    ConditionalFmt3.PNG

    Please note the $ in the formula it is required for it to work correctly!

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    RG, this sure looks like what I am after, however, I have downloaded the spreadsheet that you kindly attached and I am not sure I understand what is going on. I would like to understand this, so if you can add a bit of explanation as to what you have done, that would be great. I can get to the "Conditional Formatting" on the Home "Tab" and I can get to the setup for the rules, I just do not quite understand how the "rules" are derived.

    Ron M

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    Ok I'll give it a go. Please post back if I'm not clear.

    First I selected the entire range, i.e. A2:C6

    Next Enter conditional formatting. You'll actually have to do this 3 times to get all the rules in.

    1st rule: If value is P color Orange. We want to do this if there is a P or a p so we use the UPPER function to convert the value in the cell to Upper case then compare it to an upper case P thus:=$C2="P" The dollar sign in front of the C is there to keep this reference from changing as Excel evaluates for each of the cells in the range. There is no $ in front of the 2 because we want the row to change as it progresses down the column. Lastely we need to check the Stop if True box to save time, i.e. don't evaluate further if this is good.

    2nd rule: is the same except we change the value to X from P. Note that this rule will only be evaluated if the 1st rule fails.

    3rd rule: This rule is here so if you delete an X or P the color will revert back to White.

    Note when working on the rules it's a good idea to highlight the area first then the right rules will show up. You can also reorder the execution of the rules using the up and down arrows.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    I think I have it (sort of) now - I will give it a try and let you know if I have any further questions. One question though - as this "To Do" list expands and contracts, how do I ensure that the rules will also expand and contract. Can I make the Selection the three columns, rather than just a small selection as you have used in your example? Also, how would I change the "orange" colour to another colour?

    Thanks for your help with this.

    Ron M
    Last edited by Ron M; 2013-01-29 at 20:59.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    You BET!
    ConditionalFmt3.PNG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    and for my, hopefully, last question - how can I change the colour of the selections if I don't like the one I choose, or I want to use this approach somewhere else with different colours. Thanks.

    Ron M

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    Just edit the rule and use the format box to select a new FILL color.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    Thanks RG. I appreciate your help with this.

    Ron M

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •