If Statement
 

Is there a way to produce an IF statemnt that relates to the Fill Color of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2, IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was unable
to find it.

Thanks in advance for help.

You need a custom function for this. There is one at
http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DME" <craigjoseathotmaildotcom wrote in message
...
Is there a way to produce an IF statemnt that relates to the Fill Color of
a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2, IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was
unable
to find it.

Thanks in advance for help.

Not built in, but there are ways using VBA

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


Regards,

Peo Sjoblom

"DME" wrote:

Is there a way to produce an IF statemnt that relates to the Fill Color of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2, IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was unable
to find it.

Thanks in advance for help.

I opened a new workbook and went into VBA editor and entered the following
into this workbooks Microsoft Workbook Objects section:


Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

Then typed changed the font color of cell C8 to Red. And insert the
following formula in to D8:



=CELLCOLORINDEX(A1,FALSE)

The result is #Name?.



Any suggestions as to what I may have done wrong? Thanks.



"Peo Sjoblom" wrote in message
...
Not built in, but there are ways using VBA

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


Regards,

Peo Sjoblom

"DME" wrote:

Is there a way to produce an IF statemnt that relates to the Fill Color
of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2,
IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was
unable
to find it.

Thanks in advance for help.

Ooops, I meant to say I typed =CELLCOLORINDEX(C8,FALSE)

into cell D*.

"DME" <craigjoseathotmaildotcom wrote in message
...
I opened a new workbook and went into VBA editor and entered the following
into this workbooks Microsoft Workbook Objects section:


Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

Then typed changed the font color of cell C8 to Red. And insert the
following formula in to D8:



=CELLCOLORINDEX(A1,FALSE)

The result is #Name?.



Any suggestions as to what I may have done wrong? Thanks.



"Peo Sjoblom" wrote in message
...
Not built in, but there are ways using VBA

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


Regards,

Peo Sjoblom

"DME" wrote:

Is there a way to produce an IF statemnt that relates to the Fill
Color
of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2,
IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was
unable
to find it.

Thanks in advance for help.

DME

Move the Function out of ThisWorkbook into a General Module.

InsertModule to create one.

Cut and paste the Function to that module.


Gord Dibben Excel MVP

On Tue, 25 Jan 2005 12:15:45 -0600, "DME" <craigjoseathotmaildotcom wrote:

I opened a new workbook and went into VBA editor and entered the following
into this workbooks Microsoft Workbook Objects section:


Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

Then typed changed the font color of cell C8 to Red. And insert the
following formula in to D8:



=CELLCOLORINDEX(A1,FALSE)

The result is #Name?.



Any suggestions as to what I may have done wrong? Thanks.



"Peo Sjoblom" wrote in message
...
Not built in, but there are ways using VBA

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


Regards,

Peo Sjoblom

"DME" wrote:

Is there a way to produce an IF statemnt that relates to the Fill Color
of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 * C2,
IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was
unable
to find it.

Thanks in advance for help.

Thank You! It Works!!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
DME

Move the Function out of ThisWorkbook into a General Module.

InsertModule to create one.

Cut and paste the Function to that module.


Gord Dibben Excel MVP

On Tue, 25 Jan 2005 12:15:45 -0600, "DME" <craigjoseathotmaildotcom
wrote:

I opened a new workbook and went into VBA editor and entered the
following
into this workbooks Microsoft Workbook Objects section:


Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1,1).Font.ColorIndex
Else
CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

Then typed changed the font color of cell C8 to Red. And insert the
following formula in to D8:



=CELLCOLORINDEX(A1,FALSE)

The result is #Name?.



Any suggestions as to what I may have done wrong? Thanks.



"Peo Sjoblom" wrote in message
...
Not built in, but there are ways using VBA

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


Regards,

Peo Sjoblom

"DME" wrote:

Is there a way to produce an IF statemnt that relates to the Fill
Color
of a
Cel. For instance IF(the Fill Color of A1 = Red, then I want B2 *
C2,
IF
not then b2 *d2) .

I thought I saw something in this newsgroup beefore on this but I was
unable
to find it.

Thanks in advance for help.