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.

See the ColorIndex function he
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.

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.

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"
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.