How can I write a formula for a column of colors, (some have numbers, some
don't) that will go down the column and look for Interior.ColorIndex 36
(light yellow) and return the number of cells down that particular color is
located?
Cell A2 Interior.ColorIndex 36? No. Could be blue, but not wanted.
Cell A3 Interior.ColorIndex 36? No. Could be white, but not wanted.
Cell A4 Interior.ColorIndex 36? No.
Cell A5 Interior.ColorIndex 36? No.
.. . . .
Cell A218 Interior.ColorIndex 36? YES! Answer in formula cell A1 would be
216. First cell is question, last cell is the color, so there are 216 other
cells with colors other than Interior.ColorIndex 36.

I've found match formulas that work with words and numbers, but I can't get
them to work with Interior.ColorIndex. A Function I think would do it, but
one would have to be written as i can't find one for colors that would do
that. UDF I think they are called, the only clue I have to that is Excel
macros, but how do you get a macro to do that? I think macros are written in
VBA.

If it were only a dozen or so columns I would just knuckle down and manually
count until I got to one I thought was that color and do a little macro to
find it's number, long and tedious, but I could do it.

This however is not the case. It's several worksheets long and over 15,000
columns (around 15,537? I'm a little fuzzy there.)

Please, please help.
--
Many Thanks.

Here is the UDF:

'========
Function FindColor(r As Range, x As Integer) As Integer
Application.Volatile
i = 1
For Each c In r
If c.Interior.ColorIndex = x Then
FindColor = i
Exit For
End If
i = i + 1
Next
End Function
'=========


To install, right click on sheet tab, view code. Go to Insert - module,
paste code in. Vlose Visual Basic Editor.

In your workbook, formula is:
=FindColor(A2:A500,36)

If color is not found, returns 0. Note that reformatting the cell will not
cause a recalculation to occur, so you would need ti hit F9 to recalc.
Additionally, remember that conditional formats and cell formats are not the
same thing.

--
Best Regards,

Luke M
"Linda" wrote in message
...
How can I write a formula for a column of colors, (some have numbers, some
don't) that will go down the column and look for Interior.ColorIndex 36
(light yellow) and return the number of cells down that particular color
is
located?
Cell A2 Interior.ColorIndex 36? No. Could be blue, but not wanted.
Cell A3 Interior.ColorIndex 36? No. Could be white, but not wanted.
Cell A4 Interior.ColorIndex 36? No.
Cell A5 Interior.ColorIndex 36? No.
. . . .
Cell A218 Interior.ColorIndex 36? YES! Answer in formula cell A1 would
be
216. First cell is question, last cell is the color, so there are 216
other
cells with colors other than Interior.ColorIndex 36.

I've found match formulas that work with words and numbers, but I can't
get
them to work with Interior.ColorIndex. A Function I think would do it,
but
one would have to be written as i can't find one for colors that would do
that. UDF I think they are called, the only clue I have to that is Excel
macros, but how do you get a macro to do that? I think macros are written
in
VBA.

If it were only a dozen or so columns I would just knuckle down and
manually
count until I got to one I thought was that color and do a little macro to
find it's number, long and tedious, but I could do it.

This however is not the case. It's several worksheets long and over
15,000
columns (around 15,537? I'm a little fuzzy there.)

Please, please help.
--
Many Thanks.