Select cell by color

[Gene Augustin]

MAC Powerbook G4, OS 10.5.8
Office 2004, Excel 2004 Version 11.5.6


I would like to do:

=If(cell A7 colorindex=7, "Do something", "Do something else")

Don't know how to format the if "logical test" to use the color of the cell

Gene

 

[JE McGimpsey]

MAC Powerbook G4, OS 10.5.8
Office 2004, Excel 2004 Version 11.5.6


I would like to do:

=If(cell A7 colorindex=7, "Do something", "Do something else")

Don't know how to format the if "logical test" to use the color of the cell

Functions don't have access to formatting directly.

You can do this with a User Defined Function, but there are several
caveats...

First, put this macro in a regular code module (Option+F11 to enter the
VBE, choose Insert/Module, then type or paste):

Public Function MyColor() As Long
If TypeName(Application.Caller) = "Range" Then
MyColor = Application.Caller.Interior.ColorIndex
End If
End Function

(Option F11 to return to XL).

Then use

=IF(MyColor()=7,"Do something", "Do something else")

Caveat 1: Changing format doesn't trigger a calculation event, so
changing cell interior colors won't result in a change in the value. You
can cause the sheet to recalculate by typing CMD=

Caveat 2: Cells are only recalculated if they contain a volatile
function or if their precedents change. To make the function above
volatile, use something like:

=IF(MyColor=7+RAND()*0,"Do something", "Do something else")

Now that function will recalculate if you type CMD=

 

[Gene Augustin]

I copied the code into a new user module, and copied both versions of the
=if into the spreadsheet in a cell adjacent to the colored cell. Get "
#NAME? " Error in cell with if statement.
How does excel know that I'm testing the adjacent cell (or a cell 5 columns
over)? Do I have to change the "range" in the macro?

I understand the "CMD=" forces recalculate, but since there are errors it
doesn't do anything.

Gene

 

[JE McGimpsey]

I copied the code into a new user module, and copied both versions of the
=if into the spreadsheet in a cell adjacent to the colored cell. Get "
#NAME? " Error in cell with if statement.
How does excel know that I'm testing the adjacent cell (or a cell 5 columns
over)? Do I have to change the "range" in the macro?

I understand the "CMD=" forces recalculate, but since there are errors it
doesn't do anything.

Couple of issues here.

The "#NAME?" error means that the function name isn't recognized by XL.
Are you sure you're spelling the function name correctly?

Second, the example function was written to evaluate the color of the
cell it's called from.

To evaluate a different cell, one way:

Public Function CellColor(ByRef rng As Range) As Long
CellColor = rng.Interior.ColorIndex
End Function


Call as

=IF(CellColor(A7) = 6, "True Branch", "False Branch")

 

[Bob Greenblatt]

MAC Powerbook G4, OS 10.5.8
Office 2004, Excel 2004 Version 11.5.6


I would like to do:

=If(cell A7 colorindex=7, "Do something", "Do something else")

Don't know how to format the if "logical test" to use the color of the cell

Gene

You can't do this directly. There are 2 major problems with you
assumptions. First, how is the color being set? conditional format? If
so, set a "flag" in a hidden column to indicate the color, then test on
this flag. Second "do something" is not possible. A formula in a
worksheet can only return a value to the cell containing the formula.
You say you are using 2004, if you mean to be doing this in VBA than yes
it is possible, but you need to explain exactly what the data is and
what you want so we can help.