Finding last occurence of Interior.ColorIndex 36

[Linda]

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.

 

[Don Guillett]

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

 

[Linda]

--
Many Thanks.

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
(e-mail address removed)

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.