+ Reply to Thread
Results 1 to 4 of 4

Is there a logical test for a cell's format instead of contents

  1. #1
    Bruce
    Guest

    Is there a logical test for a cell's format instead of contents

    Microsoft Excel 2003
    I have a list of names for a roster in a single column covering several
    months. The information is imported from another source (MS Access) which is
    maintained by someone else and the only distinction between status (i.e.
    rostered, available, unavailable, absent) is the cell format (i.e. colour).
    I want to use an IF_statement to identify the different statuses so that I
    can produce a summary report.
    e.g. I could enter this formula (if there a logical test for a cell's format
    instead of contents) in cell B5:
    IF(A5 cell colour is blue,"Rostered",IF(A5 cell colour is
    green,"Available",IF(A5cell colour is grey,"unavailable","absent")))
    I could then use a lookup function to create my summary report.

  2. #2
    Rowan Drummond
    Guest

    Re: Is there a logical test for a cell's format instead of contents

    See the ColorIndex function here:
    http://xldynamic.com/source/xld.ColourCounter.html

    If you copy this to a normal module you can then create a formula
    similar to:
    =IF(colorindex(A3)=41,"Rostered","other")

    Hope this helps
    Rowan

    Bruce wrote:
    > Microsoft Excel 2003
    > I have a list of names for a roster in a single column covering several
    > months. The information is imported from another source (MS Access) which is
    > maintained by someone else and the only distinction between status (i.e.
    > rostered, available, unavailable, absent) is the cell format (i.e. colour).
    > I want to use an IF_statement to identify the different statuses so that I
    > can produce a summary report.
    > e.g. I could enter this formula (if there a logical test for a cell's format
    > instead of contents) in cell B5:
    > IF(A5 cell colour is blue,"Rostered",IF(A5 cell colour is
    > green,"Available",IF(A5cell colour is grey,"unavailable","absent")))
    > I could then use a lookup function to create my summary report.


  3. #3
    Gary''s Student
    Guest

    RE: Is there a logical test for a cell's format instead of contents

    You could insert a very tiny UDF that returns the cells' background color:

    Function clr(R As Range) As Integer
    With R.Interior
    clr = .ColorIndex
    End With
    End Function

    So that if the background color of A1 is red, then =clr(A1) would return 3
    ______________________________________
    Gary's Student


    "Bruce" wrote:

    > Microsoft Excel 2003
    > I have a list of names for a roster in a single column covering several
    > months. The information is imported from another source (MS Access) which is
    > maintained by someone else and the only distinction between status (i.e.
    > rostered, available, unavailable, absent) is the cell format (i.e. colour).
    > I want to use an IF_statement to identify the different statuses so that I
    > can produce a summary report.
    > e.g. I could enter this formula (if there a logical test for a cell's format
    > instead of contents) in cell B5:
    > IF(A5 cell colour is blue,"Rostered",IF(A5 cell colour is
    > green,"Available",IF(A5cell colour is grey,"unavailable","absent")))
    > I could then use a lookup function to create my summary report.


  4. #4
    Gord Dibben
    Guest

    Re: Is there a logical test for a cell's format instead of contents

    Bruce

    Only by using VBA.........User Defined Function

    See Chip Pearson's site for info and code.

    http://www.cpearson.com/excel/colors.htm


    Gord Dibben Excel MVP

    On Wed, 16 Nov 2005 16:18:06 -0800, "Bruce" <[email protected]>
    wrote:

    >Microsoft Excel 2003
    >I have a list of names for a roster in a single column covering several
    >months. The information is imported from another source (MS Access) which is
    >maintained by someone else and the only distinction between status (i.e.
    >rostered, available, unavailable, absent) is the cell format (i.e. colour).
    >I want to use an IF_statement to identify the different statuses so that I
    >can produce a summary report.
    >e.g. I could enter this formula (if there a logical test for a cell's format
    >instead of contents) in cell B5:
    >IF(A5 cell colour is blue,"Rostered",IF(A5 cell colour is
    >green,"Available",IF(A5cell colour is grey,"unavailable","absent")))
    >I could then use a lookup function to create my summary report.



+ 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