Another Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kris79 - 24 Feb 2007 20:02 GMT

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.

Reply to this Message

Chip Pearson - 24 Feb 2007 20:18 GMT

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.

Signature

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

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

Reply to this Message

Kris79 - 24 Feb 2007 20:53 GMT

> You can only do that with VBA code. For example,
>
[quoted text clipped - 38 lines]
>
> - 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.

Reply to this Message

KL - 24 Feb 2007 21:24 GMT

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 (Insert>Name>Define...), 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.

Signature

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

On Feb 24, 2:18�pm, "Chip Pearson" <c...@cpearson.com> wrote:

> You can only do that with VBA code. For example,
>
[quoted text clipped - 42 lines]
>
> - 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.

Reply to this Message

KL - 24 Feb 2007 21:27 GMT

you can actually reduce

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

Signature

> Hi Kris79,
>
[quoted text clipped - 66 lines]
> 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.

Reply to this Message

Kris79 - 24 Feb 2007 22:52 GMT

> you can actually reduce
>
[quoted text clipped - 90 lines]
>
> - 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.

Reply to this Message

KL - 24 Feb 2007 23:14 GMT

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

Signature

Reply to this Message

Chip Pearson - 24 Feb 2007 23:19 GMT

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

Signature

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

>> you can actually reduce
>>
[quoted text clipped - 106 lines]
> How do I figure out which color goes with which numeric digit? The 2
> colors i am using are rose and light yellow.

Reply to this Message

Herbert Seidenberg - 25 Feb 2007 01:44 GMT

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.

Reply to this Message

Kris79 - 25 Feb 2007 04:28 GMT

On Feb 24, 7:44 pm, "Herbert Seidenberg" <herbds7-ms...@yahoo.com>
wrote:

> Here is a way without VBA or Excel4 macro functions.
> Disadvantage: Each color is restricted to a maximum of 33
[quoted text clipped - 38 lines]
> 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.

Reply to this Message