I am trying to build a spreadsheet containing cells with 4 different
background colors. Red, yellow, green, and white. I want to graph the
results of the spreadsheet based on background color for each cell. I
extracted the following formula via another discussion but do not know how to
use it (call it or invoke it).

Function BackColour(r As Range)
Select Case r.Interior.ColorIndex
Case Is = 6 'Yellow
BackColour = 1
Case Is = 4 'Green
BackColour = 2
Case Is = 3 'red
BackColour = 3
Case Else
BackColour = "Not Defined"
End Select
End Function

Can someone tell me how to apply this and use the calculated results in a
graph? Thanks.

Use it just as you use any Excel formula...

First part use this in the cell you want to find the Back Color
(assuming the function is in the same workbook otherwise precede it with the
name of the workbook)

=BackColor(A1)

Once you get the colors then you can make a graph in the normal way.

"DeviceConnect" wrote:

I am trying to build a spreadsheet containing cells with 4 different
background colors. Red, yellow, green, and white. I want to graph the
results of the spreadsheet based on background color for each cell. I
extracted the following formula via another discussion but do not know how to
use it (call it or invoke it).

Function BackColour(r As Range)
Select Case r.Interior.ColorIndex
Case Is = 6 'Yellow
BackColour = 1
Case Is = 4 'Green
BackColour = 2
Case Is = 3 'red
BackColour = 3
Case Else
BackColour = "Not Defined"
End Select
End Function

Can someone tell me how to apply this and use the calculated results in a
graph? Thanks.

I also forgot to mention that I have text in each cell that I want to retain.
How do keep that in tact while assigning the function to the cell?

You can't have your cake and eat it too :-)

If the text in A1 is TEST and color is YELLOW then do you want B1 to say
TEST-YELLOW?

If yes then use
=A1 & "-" & BackColor(A1)

If not then give an example so that we can understand your requirement.

"DeviceConnect" wrote:

I also forgot to mention that I have text in each cell that I want to retain.
How do keep that in tact while assigning the function to the cell?

Cake? I love cake...but it appears I can not have it if my suspicions are
correct. What I have is a series of cells with the date in them. Each cell
background color (yellow, red, or green) corresponds to product delivery
dates. If the date has come and gone and the project not completed the
background color is red, if the delivery date is in the near future and the
project still in progress the color is yellow, and if the project was
delivered on time and the date has come and gone the background color is
green. What I want to do is group the not done, in progress, completed
deliverables by color, not by date. That way I can report the 3 categories
of completion each as one group of data. I still need both pieces of
information for visual verification.

"Sheeloo" wrote:

You can't have your cake and eat it too :-)

If the text in A1 is TEST and color is YELLOW then do you want B1 to say
TEST-YELLOW?

If yes then use
=A1 & "-" & BackColor(A1)

If not then give an example so that we can understand your requirement.

"DeviceConnect" wrote:

I also forgot to mention that I have text in each cell that I want to retain.
How do keep that in tact while assigning the function to the cell?

:-)


"DeviceConnect" wrote:

Cake? I love cake...but it appears I can not have it if my suspicions are
correct. What I have is a series of cells with the date in them. Each cell
background color (yellow, red, or green) corresponds to product delivery
dates. If the date has come and gone and the project not completed the
background color is red, if the delivery date is in the near future and the
project still in progress the color is yellow, and if the project was
delivered on time and the date has come and gone the background color is
green. What I want to do is group the not done, in progress, completed
deliverables by color, not by date. That way I can report the 3 categories
of completion each as one group of data. I still need both pieces of
information for visual verification.

"Sheeloo" wrote:

You can't have your cake and eat it too :-)

If the text in A1 is TEST and color is YELLOW then do you want B1 to say
TEST-YELLOW?

If yes then use
=A1 & "-" & BackColor(A1)

If not then give an example so that we can understand your requirement.

"DeviceConnect" wrote:

I also forgot to mention that I have text in each cell that I want to retain.
How do keep that in tact while assigning the function to the cell?