On Feb 24, 3:27?pm, "KL" wrote:
you can actually reduce

=IF(CELLCOLOR=5,1,0)
to
=--(CELLCOLOR=5)

--
KL
[MVP - Microsoft Excel]
RU:http://www.mvps.ru/Program/Default.aspx
ES:http://mvp.support.microsoft.com/?LN=es-es
EN:http://mvp.support.microsoft.com/?LN=en-us
Profile:https://mvp.support.microsoft.com/pr...-4AB9-ADDA-9E6...



"KL" wrote in l...
Hi Kris79,

There is a fairly complex way of achieving the same result using defined names with Excel4 macrofunctions:
http://www.jkp-ads.com/Articles/ExcelNames09.htm

A simplier but less flexible way is just to use macrofuntion with relative cell reference...
- select the cell [B1]
- define a name (InsertNameDefine...), say CELLCOLOR, with the formula =GET.CELL(63+0*now(),A1)
- asuming the colored cell is [D5], in [E5] write the following formula: =IF(CELLCOLOR=5,1,0) where 5 is the colorindex you are
looking for

This one has one significant dowside - in XL2000 or earlier, if you attempt to copy a cell that contains a formula with such a
name from one sheet to another, Excel will shut down with the consequent loss of unsaved data.

In all cases, both solutions would not recalculate upon cell color change, as this action doesn't drive the recalc event in
Excel. The formula result will update only upon the next action that does drive the recalc.

My suggestion: instead of conditioning the result by the color, see if you can turn the logic around and condition the color by
the result.

--
KL
[MVP - Microsoft Excel]
RU:http://www.mvps.ru/Program/Default.aspx
ES:http://mvp.support.microsoft.com/?LN=es-es
EN:http://mvp.support.microsoft.com/?LN=en-us
Profile:https://mvp.support.microsoft.com/pr...-4AB9-ADDA-9E6...

"Kris79" wrote in oglegroups.com...
On Feb 24, 2:18?pm, "Chip Pearson" wrote:
You can only do that with VBA code. For example,

Public Function ColorIndexOfCell(Rng As Range, _
? ? ? ? Optional OfFont As Boolean = False) As Variant
? ? Application.Volatile True
? ? If Rng.Cells.Count 1 Then
? ? ? ? ColorIndexOfCell = CVErr(xlErrRef)
? ? Else
? ? ? ? If OfFont = True Then
? ? ? ? ? ? ColorIndexOfCell = Rng.Font.ColorIndex
? ? ? ? Else
? ? ? ? ? ? ColorIndexOfCell = Rng.Interior.ColorIndex
? ? ? ? End If
? ? End If
End Function

This will return the ColorIndex (a value between 1 and 56, or
xlColorIndexAutomatic = -4105 or xlColorIndexNone = -4142) of the specified
cell. If the OfFont parameter is True, the function return the ColorIndex of
the font. If OfFont is omitted or False, it return the ColorIndex of the
fill. ? You can then call this function from a worksheet cell with a formula
like

=ColorIndexOfCell(A1,FALSE) = 5

to return TRUE or FALSE indicating whether A1 has a fill color of 5 =
default blue.

Seewww.cpearson.com/excel/colors.htmformore info.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLCwww.cpearson.com
(email address is on the web site)

"Kris79" wrote in message

groups.com...

Is it possible to write an if function on a background color. Say if
a1 has a background color of red return 1 if true and return 0 if
false.- Hide quoted text -

- Show quoted text -

VBA is my weekpoint i have no clue what that means or how to get it to
relate to what i am trying to do.- Hide quoted text -

- Show quoted text -

How do I figure out which color goes with which numeric digit? The 2
colors i am using are rose and light yellow.