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.

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.

See www.cpearson.com/excel/colors.htm for more info.


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

"Kris79" wrote in message
oups.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.

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.htmfor more info.

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

"Kris79" wrote in message

oups.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.

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...A-9E6C73C09A36


"Kris79" wrote in message ups.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.htmfor more info.

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

"Kris79" wrote in message

oups.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.

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...A-9E6C73C09A36


"KL" wrote in message ...
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...A-9E6C73C09A36


"Kris79" wrote in message ups.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.htmfor more info.

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

"Kris79" wrote in message

oups.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.

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.

"Kris79" wrote in message ups.com...
How do I figure out which color goes with which numeric digit? The 2
colors i am using are rose and light yellow.

Using the method at which I pointed you:

if [D1] is the cell whose color index you want to know, then in [E1] write =CELLCOLOR

Again, if there is a logical reason for a specific color in a specific cell, then most probably you can use that logic to
a) calculate Conditional Formats (since you are only using 2 colors)
b) add conditions to your formula without trying to do something Excel is not natively able to do.

--
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...A-9E6C73C09A36

Kris,

How do I figure out which color goes with which numeric digit?

There isn't a particularly good way to do this. One way is to run a
procedure to fill in the cells with the colors. E.g,

Sub ShowColors()
Dim N As Long
For N = 1 To 56
Cells(N, "A").Interior.ColorIndex = N
Next N
End Sub

After you run that code, the colors will be filled in Column A. The row
number is that color's ColorIndex in the default pallet.

Another way is to select a cell that has the color you are interested in and
type the following followed by the Enter key in the Immediate Window
(CTRL+G) of the VBA Editor (ALT+F11):

?ActiveCell.Interior.ColorIndex



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


"Kris79" wrote in message
ups.com...
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.

Here is a way without VBA or Excel4 macro functions.
Disadvantage: Each color is restricted to a maximum of 33
non-contiguous areas.
This method consists of finding and naming
all cells of a certain color.
The if() function then tests if a cell belongs to a given name.
Assume this list is at A1 with background colors matching
the cell content:

green1
orange1
red1
blue1
yellow1
red2
yellow2
green2
yellow3
orange2
blue2
red3
green3
yellow4
orange3
blue3
yellow5
green4
red4
orange4

Select A1:A20 Edit Find Find what: clear content
Options Format Choose format from cell
click a sample cell (say A1) Find All
SHIFT+END (this will select A1, A8, A13, A18)
Insert Name Define Names in workbook: GreenC
or enter GreenC into the Name Box.
At B1 enter this sample if() formula and copy down:
=IF(ISERROR(GreenC $A1),"",$A1)
All the green cells will have an entry in column B.
To maximize the number of named colored cells/areas,
keep the sheet name short (one letter) and work in the area
of the sheet that has single row/column digits/letters.

On Feb 24, 7:44 pm, "Herbert Seidenberg"
wrote:
Here is a way without VBA or Excel4 macro functions.
Disadvantage: Each color is restricted to a maximum of 33
non-contiguous areas.
This method consists of finding and naming
all cells of a certain color.
The if() function then tests if a cell belongs to a given name.
Assume this list is at A1 with background colors matching
the cell content:

green1
orange1
red1
blue1
yellow1
red2
yellow2
green2
yellow3
orange2
blue2
red3
green3
yellow4
orange3
blue3
yellow5
green4
red4
orange4

Select A1:A20 Edit Find Find what: clear content
Options Format Choose format from cell
click a sample cell (say A1) Find All
SHIFT+END (this will select A1, A8, A13, A18)
Insert Name Define Names in workbook: GreenC
or enter GreenC into the Name Box.
At B1 enter this sample if() formula and copy down:
=IF(ISERROR(GreenC $A1),"",$A1)
All the green cells will have an entry in column B.
To maximize the number of named colored cells/areas,
keep the sheet name short (one letter) and work in the area
of the sheet that has single row/column digits/letters.

Thanks for all your help all.