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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|