Hello,
I've been given a workbook to convert into Access. The users have
manually formated row colors to indicate the status of an order. Is
there a way to set the value of a cell depending on its color, as in
=IF(COLOR=GRAY,CANCELED,)? Of course I have about 6 colors to deal with
,is there a way to nest this?

Thanks,

Todd

First enter a little function to return the color of a cell:

Function colorcode(cell As Range) As Integer
colorcode = cell.Interior.ColorIndex
End Function

Then if you want to set the value of A1 based upon its color, enter the
formula:

=if(colorcode(A1)=15,"CANCELLED","")

You will probably need to re-calculate the worksheet if you change cell
colors because Excel may not realize the function colorcode will have to be
re-executed.
--
Gary's Student


"tjack13" wrote:

Hello,
I've been given a workbook to convert into Access. The users have
manually formated row colors to indicate the status of an order. Is
there a way to set the value of a cell depending on its color, as in
=IF(COLOR=GRAY,CANCELED,)? Of course I have about 6 colors to deal with
,is there a way to nest this?

Thanks,

Todd

On Sun, 03 Sep 2006, tjack13 wrote:

I've been given a workbook to convert into Access. The users have
manually formated row colors to indicate the status of an order. Is
there a way to set the value of a cell depending on its color, as in
=IF(COLOR=GRAY,CANCELED,)? Of course I have about 6 colors to deal with
,is there a way to nest this?

Yes. What you are looking for is Cells(i,j).Interior.ColorIndex =
The color indexes are integers. I'm not sure where it is documented what
they are, but you can experiment.

Don <www.donwiss.com (e-mail link at home page bottom).

Thanks, that did it. Since there were 8 colors, I was able to get by
with the seven levels of IF nesting. Do you know of a way to deal with
more color options?

Todd

Gary''s Student wrote:
First enter a little function to return the color of a cell:

Function colorcode(cell As Range) As Integer
colorcode = cell.Interior.ColorIndex
End Function

Then if you want to set the value of A1 based upon its color, enter the
formula:

=if(colorcode(A1)=15,"CANCELLED","")

You will probably need to re-calculate the worksheet if you change cell
colors because Excel may not realize the function colorcode will have to be
re-executed.

On Sun, 03 Sep 2006, tjack13 wrote:

I've been given a workbook to convert into Access. The users have
manually formated row colors to indicate the status of an order. Is
there a way to set the value of a cell depending on its color, as in
=IF(COLOR=GRAY,CANCELED,)? Of course I have about 6 colors to deal with
,is there a way to nest this?

Amd one more thing. The .Interior.ColorIndex ignores any conditional
formatting. It always returns the core color.

Don <www.donwiss.com (e-mail link at home page bottom).

You can use the CHOOSE() function. See Excel Help. CHOOSE can handle up to
29 different picks.
--
Gary's Student


"tjack13" wrote:

Thanks, that did it. Since there were 8 colors, I was able to get by
with the seven levels of IF nesting. Do you know of a way to deal with
more color options?

Todd

Gary''s Student wrote:
First enter a little function to return the color of a cell:

Function colorcode(cell As Range) As Integer
colorcode = cell.Interior.ColorIndex
End Function

Then if you want to set the value of A1 based upon its color, enter the
formula:

=if(colorcode(A1)=15,"CANCELLED","")

You will probably need to re-calculate the worksheet if you change cell
colors because Excel may not realize the function colorcode will have to be
re-executed.