Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    return the cell colour as useful information? (97 SR2)

    I am pleased to have received a spreadsheet which has been beautifully colour-coded (with about six different colurs meaning various levels of importance).

    Unfortunately the cell has been left blank, and the fill colour has been manually applied.
    Is there a way I can write a cell formula so I can turn this pretty data into something a little more useful? Anything would do (say the colour number?) Is there any way to do this outside of VBA?

    Thanks in advance,
    Marty

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: return the cell colour as useful information? (97 SR2)

    I ido not think that can be done without ewcourse to VBA. The CELL() function does not return this type of information, though it can provide other formatting data.

    However thye following simple enough function will return the information you require.<pre>Function CellColor(rng As Range)
    CellColor = IIf(rng.Interior.ColorIndex < 0, 0, rng.Interior.ColorIndex)
    End Function</pre>

    To get the color index of cell A1 you would use =CellColor(A1) in some other cell. A result of zero (0) indicates no color applied.

    Andrew C

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: return the cell colour as useful information? (97 SR2)

    Marty
    Have you tried Conditional Formatting? If it's not in your menu, try bringing it up via TOOLS | CUSTOMIZE in the the box, select the COMMANDS tab, highlight FORMAT and scroll down until you find CONDITIONAL FORMATING. Drag it up to either the toolbar or menu and position it where you want it.
    I use it so often, that I put mine in the toolbar and created my own button for it.

    Although you can only use 3 conditional statements, including the default setting of the cell, you actually have 4 ways to display the data.

    One example, that I use automatically gives me the status of certain items depending on the formatting of the cell or data. At a glance I can tell if an vehicle is ready for use, being repaired, parts on order, or undergoing maintenance.

    If you play around with it, you can figure out how to replicate the formating on each additional row or column. Using the example I gave, whenever I get a new vehicle, I simply hit another macro button to create a new row using a hidden row that has everything set up. It's quick and dirty and minimizes the use of writing code if that's not your forte.

    AJF

Posting Permissions

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