Windows 7 - Select cell by color

Asked By Gene Augustin

25-Jan-10 05:08 PM

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

26-Jan-10 07:17 AM

Functions do not 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 does not trigger a calculation event, so
changing cell interior colors will not 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=

28-Jan-10 05:14 PM

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 "
How does excel know that I am 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
does not do anything.

Gene




On 1/26/10 7:17 AM, in article
jemcgimpsey-FB7B35.05172426012010@news.microsoft.com, "JE McGimpsey"

29-Jan-10 07:29 AM

Couple of issues here.

The "#NAME?" error means that the function name is not recognized by XL.
Are you sure you are spelling the function name correctly?

Second, the example function was written to evaluate the color of the
cell it is 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")

05-Mar-10 08:59 AM

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

--

--
Bobgreenblattatmsndotcom