A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rephrasing Interior.ColorIndex 36 question.



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 12:21 AM posted to microsoft.public.excel.newusers
Linda
external usenet poster
 
Posts: 661
Default Rephrasing Interior.ColorIndex 36 question.

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.
  #2  
Old May 28th, 2010, 01:49 PM posted to microsoft.public.excel.newusers
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Rephrasing Interior.ColorIndex 36 question.

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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.