Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get Cell Value Based on Cell Color (Excel 97 SR2)

    Hi everyone,
    Is there a way to get a cell's value based on its color? I have highlighted some cells in bright yellow. I would like to enter a formula that will look at a column of values and enter only those cells that are yellow. In other words, the column that will have the formula will contain only bright yellow values.

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    Come again?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    This can only be done with VBA. The simplest way may be to write a formula which uses the custom function <!post=here,95626>here<!/post> such as in an IF() statement:

    =IF(CellColor(address)=6,something,something else)

    or you can examine these Interior.ColorIndex posts for something more precise.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)


  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    I have attached a spreadsheet showing what I want to do. In short, I would like to enter a function or run a macro that would look at column E and put a value in column F only if the cell in E is yellow.

    I have read all the answers (thanks everyone) but still haven't figured out how to do it.

    TIA
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    Will this work?
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    Works like a charm!!!! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Thanks!!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    John,
    I want to make the CellColor Function available to all spreadsheets so I copied the Function from the VBA module in your spreadsheet to my Personal.xls file. However, I still am getting #NAME? as a result. Is there something else I need to do to be able to use it in every file?

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  9. #9
    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: Get Cell Value Based on Cell Color (Excel 97 SR2)

    Hi Sherry,
    Are you entering the function as
    =Personal.xls!CellColor
    If it's not in an add-in, you need to prefix the function with the name of the workbook.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get Cell Value Based on Cell Color (Excel 97 SR2)

    That does it Rory.

    Thanks!!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

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