Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
7+ nested if statement? | Excel Worksheet Functions | |||
Statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
IF Statement difficulty | Excel Worksheet Functions |