+ Reply to Thread
Results 1 to 5 of 5

Changing Cell Colors Based on Other Cell Values

  1. #1
    Registered User
    Join Date
    10-08-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    1

    Changing Cell Colors Based on Other Cell Values

    Hello guys,

    As the title states, I'm currently trying to change cell colors on a Main sheet based on whether or not a value is present on another sheet. There are 6 other sheets total, and only one value can be found on any one cell of any sheet at a time.

    So for example, if B7 on sheet 3 = 5.5, then B7 on every other sheet will be completely blank.

    Each sheet has its own color. I want the main sheet's cell color to correspond whichever sheet's cell whose value is > 0. So in the example above, if sheet 3 was green, I'd want the main sheet's B7 cell to be green.

    I consider myself an experienced visual basic programmer but I just don't know the syntax very well (or at all, really) for excel in VB. Also, it may even be doable with just the excel functions, but once again I'm not very versed in excel syntax. Also, I'm having a hard time referencing cells on other sheets to begin with.

    If anyone can help me with this issue, I'd be extremely grateful. Thanks in advance for any support!

    - Vinx

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Changing Cell Colors Based on Other Cell Values

    Hello Vinx,

    Welcome to the Forum!

    Posting a workbook is the fastest way to get answers to your questions and assures they will be correct.

  3. #3
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Changing Cell Colors Based on Other Cell Values

    To see the syntax for referring to cells on other sheets:

    1) Pick a cell on main sheet (say C5)
    2) Type "=" (without the quotation marks)
    3) Use your mouse to select tab of different sheet (you should see the tab's name in the formula bar now)
    4) Use your mouse to point to the cell on this tab that you want to check value of
    5) Type ">0" (without the quotation marks)
    6) Click <Enter> (This will put a "True" or "False" in cell C5).
    7) put your cursor in the formula bar in front of the "=" and put a "'" in front of it.


    Now you can see the syntax in cell C5 for checking to see if a cell on a different sheet > 0 and you can use that syntax inside Conditional Formatting to change the color of a cell on the main sheet.
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  4. #4
    Forum Contributor
    Join Date
    09-09-2009
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    185

    Re: Changing Cell Colors Based on Other Cell Values

    Hello Foxguy,

    I tried this just because I am trying to do something similar, except I need to compare the 2 cells and highlight if they match.

    But, besides that, I got an error when I pasted in the formula under conditional formatting. It said that you cannot reference another sheet when you are doing conditional formatting. so I deleted the sheet reference and just had it associate it with another cell on the same sheet and it worked flaelesssly. I don't know if I did something wrong or not.

  5. #5
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Changing Cell Colors Based on Other Cell Values

    I never noticed that you can't reference another sheet in conditional formatting before, but I got the same error when I tried to do it. I had never tried it before.

    So it doesn't sound like you did anything wrong.

+ 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