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  

Finding last occurence of Interior.ColorIndex 36



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 10:11 PM posted to microsoft.public.excel.newusers
Linda
external usenet poster
 
Posts: 661
Default 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.
  #2  
Old May 27th, 2010, 10:56 PM posted to microsoft.public.excel.newusers
Don Guillett[_2_]
external usenet poster
 
Posts: 607
Default Finding last occurence of Interior.ColorIndex 36

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.


  #3  
Old May 28th, 2010, 07:04 AM posted to microsoft.public.excel.newusers
Linda
external usenet poster
 
Posts: 661
Default 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

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 10:14 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.