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.