Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Upstate, South Carolina, USA
    Posts
    253
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditionally changing cell color (2002)

    <P ID="edit" class=small>Edited by gwhitfield on 29-Nov-01 06:42.</P>Hyperlinks added

    I read Andrew's <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=30477&page=&v iew=&sb=&o=&vc=1#Post30477>post 30477</A> on "Summing Conditionally Formatted Colors" with interest since I've been trying to figure out a way to change the cell shading color based upon a set of conditions. I was using a not-so-simple, brute-force set of IF-THEN conditionals:

    IF(Ashburn!$B32="Building F",IF(Ashburn!$G32="Operational",IF(Ashburn!$D32=" Y","X-S","X"),
    IF(Ashburn!$F32<Summary!$E$1,IF(Ashburn!$D32="Y"," X-S","X"),Ashburn!$F32)),IF(Ashburn!$H32<Summary!$E$ 1,IF(Ashburn!$D32="Y","X-S","X"),Ashburn!$H32))

    which as I recall breaks down like this

    IF(Ashburn!$B32="Building F",
    THEN-IF
    IF(Ashburn!$G32="Operational",
    THEN
    IF(Ashburn!$D32="Y","X-S","X"),
    ELSE-IF
    IF(Ashburn!$F32<Summary!$E$1,
    THEN
    IF(Ashburn!$D32="Y","X-S","X"),Ashburn!$F32)),
    ELSE
    IF(Ashburn!$H32<Summary!$E$1, IF(Ashburn!$D32="Y","X-S","X"), Ashburn!$H32))

    Anyway, what I wanted to do was set the cell shade color to tan if the resulting cell contents was "X-S".

    Any ideas?

    Many thanks,

    - Al

    P.S. It appears that the pre-post formatter removes leading spaces on the lines. Too bad since I had formattted the "breaks down" section with leading spaces for easier reading...
    Al
    "Do or do not do. There is no try." -- Yoda
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditionally changing cell color (2002)

    How many different formats do you want? If it's just the one tan fill, use conditional formatting such that cell value is equal to ="X-S". If you need more than three, you'll need to go VBA.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Upstate, South Carolina, USA
    Posts
    253
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    Three or less. But which function can change the cell fill color?
    Al
    "Do or do not do. There is no try." -- Yoda
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  4. #4
    New Lounger
    Join Date
    Nov 2001
    Location
    Amsterdam
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    Hello

    This is the syntax you need to change a cell's background colour:

    worksheet.Cells(1, 1).Interior.ColorIndex = 37 'light blue, 3rd from right of bottom row of colour picker

    Hope this helps

    Mark

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditionally changing cell color (2002)

    Are you doing this manually or with VBA? If manually, in the Conditional Format dialog, click on the Format Button, the Patterns tab, then select your color.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Upstate, South Carolina, USA
    Posts
    253
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    Well... I was hoping to accomplish all this in a cell formula without resorting to VBA ... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>. Something like ChangeCellColor(if-this-expression-evaluates-to-"X-S", NoFill). I'm actually doing this for a colleague who is changing the cell color manually. The reason I'd like to avoid VBA is that it creates one more step, i.e., forcing the macro to run. Thanks.
    Al
    "Do or do not do. There is no try." -- Yoda
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Upstate, South Carolina, USA
    Posts
    253
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    Oops ... I meant ChangeCellColor(if-this-expression-evaluates-to-"X-S", Tan, NoFill).
    Al
    "Do or do not do. There is no try." -- Yoda
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditionally changing cell color (2002)

    Hi,
    What John is saying is that you don't need a formula to do the shading. Select the cell that has that monster formula in it, then choose Format-Conditional Formatting from the menubar. Choose Cell Value Is, Equal To, and then type ="X-S" in the third box. Then click on the Format... button, choose the patterns tab and pick your colour. This will then shade your cell with your chosen colour only if your formula evaluates to "X-S".
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    If you can't do it with conditional formatting (ie. you have more than three conditions), then you can put the VBA code in the worksheet change event routine. The code will run automatically any time one of the cells is changed. No extra steps.
    Legare Coleman

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditionally changing cell color (2002)

    I think Rory & Legare have answered your question, but just in case, no, in Excel there is no =FUNCTION() formulaic way to set fill color, but you can set it through conditional formatting without using VBA.

    Are we there yet? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Upstate, South Carolina, USA
    Posts
    253
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditionally changing cell color (2002)

    Thank you everyone. I got so wrapped up in "formularizing" that I didn't even see the Conditional Formatting capability. This will work just fine and, thanks to Legare, I now know how to make VBA code perform cell updates upon change. This concludes what I needed.
    Al
    "Do or do not do. There is no try." -- Yoda
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>

Posting Permissions

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