+ Reply to Thread
Results 1 to 7 of 7

If statement to change cell fillcolor

  1. #1
    Registered User
    Join Date
    02-02-2010
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    36

    If statement to change cell fillcolor

    Hi All

    Is it possible to use an IF statement to change a cells fills color? I am wanting to change a cells fill color if my IF statement is true, and if false then make it nofill.

    Thanks for any assistance

    Redders
    Last edited by redders; 03-25-2010 at 02:24 PM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: If statement to change cell fillcolor

    yes read the help file on conditional formatting
    free tutorial here
    http://www.free-training-tutorial.co...ormatting.html
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    02-02-2010
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: If statement to change cell fillcolor

    Hi martindwilson

    Thanks for pointing me to those great newbie tutorials and I am aware of conditional formatting but it wont do what I want in this case, and thats probably because I explained myself badly.

    What my if statement is doing on my worksheet is looking at the corresponding cell on another worksheet and filling the cell color on my current worksheet depending on what is on the other worksheet. eg. IF c2 on the other worksheet is 'B' then fillcolor otherwise nofill.

    If there is a way for conditional formatting to do this without the IF statement then I would very much appreciate your help.

    Thanks

    Redders

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,202

    Re: If statement to change cell fillcolor

    Conditional Formatting does not allow you to directly reference a cell in another worksheet. However, if you name the cell, you can use the name of that cell. For example, in conditional formatting for A1 in Sheet1;
    =ISTEXT(Sheet2!A2) is not allowed but if you name sheet2!A2 ="example"
    =ISTEXT(example) works.
    Are you familiar with naming cells?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    02-02-2010
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: If statement to change cell fillcolor

    Hi ChemistB

    Is it possible to name a range in the other worksheet and use that? The other worksheet has a range of cells from C-NB so its quite long and this new worksheet needs to look at every individual cell and filcolor depending on whats on that other worksheet? I have an idea on how to name cells, just having to name each one would be excessive.

    Sorry for my ignorance, just way not an excel guru thats why you guys here are such a great resource.

    Redders

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,202

    Re: If statement to change cell fillcolor

    Sounds doable. Can you upload a dummy workbook with an example of what you will have on sheet1 and sheet2? That way I can see more clearly what you are going for (e.g. can it match any cell in sheet2 or the corresponding cell location only.).

  7. #7
    Registered User
    Join Date
    02-02-2010
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: If statement to change cell fillcolor

    Hi ChemistB

    I think I shall mark this one as solved. I am happy to have my second worksheet return a value, as well as a fillcolor, if the condition is true from the first worksheet. By then using conditional formatting on whether the cell contains a value or not, I also can get the fillcolor happening so for me problem solved.

    Thank you both for your assistance

    Redders

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1