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
|
|||
|
|||
Finding last occurence of Interior.ColorIndex 36
I have been searching for a formula to help me find the last time (most
recent) color 36 appears in a column of colored cells. Most of the time there is no typed information and when there is, it is not the same for every color 36 cell. The cells are not conditionally formatted. C. Pearson's site is great, but I can't find what I need. I don't want to change color, I don't want to know how many times it shows up, I just want to find the last time it is in the column. I could count down to find it, but there are over 15,000 columns spread over several worksheets. I have Excel 2003. I'm pretty formula illiterate, it takes me days to get a formula to work and even then I'm not sure how I got it to work. In my reading it seems VBA (?) would work, I think it is what macros use and I have cut and pasted from my macros to make things work, but I'm getting nowhere fast with this problem. -- Many Thanks. |
#3
|
|||
|
|||
Finding last occurence of Interior.ColorIndex 36
-- Many Thanks. "Don Guillett" wrote: The trick is to work from the bottom up. Change col "j" to suit Sub FindLastColor36_SAS() mc = "j" For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1 'MsgBox Cells(i, mc).Interior.ColorIndex If Cells(i, mc).Interior.ColorIndex = 36 Then Exit For End If Next i MsgBox "Found at row " & i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Linda" wrote in message ... I have been searching for a formula to help me find the last time (most recent) color 36 appears in a column of colored cells. Most of the time there is no typed information and when there is, it is not the same for every color 36 cell. The cells are not conditionally formatted. C. Pearson's site is great, but I can't find what I need. I don't want to change color, I don't want to know how many times it shows up, I just want to find the last time it is in the column. I could count down to find it, but there are over 15,000 columns spread over several worksheets. I have Excel 2003. I'm pretty formula illiterate, it takes me days to get a formula to work and even then I'm not sure how I got it to work. In my reading it seems VBA (?) would work, I think it is what macros use and I have cut and pasted from my macros to make things work, but I'm getting nowhere fast with this problem. -- Many Thanks. Many thanks for your reply. At least I had the Rows.Count, Next and Interior.ColorIndex correct! LOL. I've been working on this for months. I have John Walkenback's book 'Excel 2003 Formulas' and I was thinking about a loop. The colors in the columns are added from the top. The newest colors are at the top and the oldest ones at the bottom. I was not specific, I'm sorry about that. I need the most recent addition, which would be at the top. Is there a book or website I could go to, to figure out what the i, mc and other letter (N = N + 1) comments in the procedures mean? I was hoping to put a formula using the function and the column range in an empty cell at the top of the range and have the function go down cell by cell looking for color 36 then when it finds it return the number of cells down it searched before finding it in the range. That number would be in the same cell as the formula. Sometimes that color is not in the range at all. Then it would return "0". (the ol' IF A3=0,0 thing) I've tried using MATCH but I can't get it to work with Interior.ColorIndex = 36. Word, letter, number, Yes, but not the cell fill. There are so many columns the cell reference in the sub routine would I have to write one for each column? I picked up this Function on Yahoo Answers and I'm trying to change it to find the first colored cell in the column, but am having no luck, so far. I know not understanding the nomeclature is a major part of my problem. Function CountColor(Rng As Range) Dim cel As Range Dim C As Long Dim N As Long For Each cel In Rng N = N + 1 If cel.Interior.ColorIndex 6 Or _ cel.Interior.ColorIndex xlNone Then C = C + 1 Next CountColor = C End Function Have I confused you enough by now? You used a good word - trick and for me this is indeed tricky. Thank you so much for responding. |
Thread Tools | |
Display Modes | |
|
|