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.
0
Utf
5/27/2010 9:11:49 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
842 Views

Similar Articles

[PageSpeed] 53

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
dguillett@gmail.com
"Linda" <Linda@discussions.microsoft.com> wrote in message 
news:B45ED6E0-1181-4308-B152-969DE0E57C39@microsoft.com...
>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. 

0
Don
5/27/2010 9:56:20 PM
-- 
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
> dguillett@gmail.com
> "Linda" <Linda@discussions.microsoft.com> wrote in message 
> news:B45ED6E0-1181-4308-B152-969DE0E57C39@microsoft.com...
> >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.
0
Utf
5/28/2010 6:04:01 AM
Reply:

Similar Artilces:

Advanced find to select Accounts with "interested" campaign responses.
Hello. Hopefully someone may be able to help with this query. I need to be able to run an advanced find query that returns every account who has a campaign response for a particular campaign, where the response code is "interested". I've tried several approaches to this, and whilst I can return all accounts that are on a particular marketing list, where is has been applied to a particular campaign, I can't get it to work to only include accounts with an "interested" campaign response logged against that campaign. I've tried things based around the following ...

How do I find a document in Word 2007?
I realize that I can search for a document using Windows, but is there a way in Word 2007 for me to search for a certain document, or a word within a document? -- jeannine In Word 2007, there is no longer a specific user interface for searching from within Word. However, to search a folder in, say, the Open dialog box, you can right-click its icon and choose Search from the context menu. -- Stefan Blom Microsoft Word MVP "jldube" <jldube@discussions.microsoft.com> wrote in message news:50E2DEC3-977E-48E4-9BC3-B7BC47A44B62@microsoft.com... >I r...

Excel is changing the last digit to 0
I am trying to enter some numbers into an excel spreadsheet and the program keeps changing my last digit to 0. These are 16 digit numbers. Is there any way to fix this? What is going on? Excel is limited to 15-digit precision. To enter longer numbers, precede the number with an apostrophe. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Heather Lisco" <HeatherLisco@discussions.microsoft.com> wrote in message news:A8BF2789-30FB-447E-BE5C-B10087281E85@microsoft.com... >I am trying to enter some numbers into an e...

Finding Outlook on Another Drive
My computer had a system crash so I hooked my hard drive up on my girlfriends computer... I need to open MY Outlook for important dates and email... How do I get to it? I can open my girlfriends Outlook because that's now on her desktop and the master hard drive... I need to open my Outlook from my drive or import the data... I know how to import export but I can't get to it to export data to a file so I can import it on her drive... I hope someone knows what I'm talking about... I need this in the next two days and I don't have time to fix or buy another computer rig...

finding a blank sheet
Does anybody have an idea of the best way to look in a workbook an delete any sheet that is completely blank? Therefore only keepin worksheets that have data in them. Thanks for your help, Crai -- Message posted from http://www.ExcelForum.com cparsons, I don't profess to have written this but have found the folowing cod which would appear to do what you want Sub Delete_EmptySheets() Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If ...

What happened to the find file command?
On Microsoft Word 2003, I could use the find file command to find Word files. I could search on words in the file and one file names. I cannot find this command in Word 2007. Please help! As far as I know, that command is no longer supported. Instead, use the Find feature in Windows. Hold the Windows key as you press F. -- Stefan Blom Microsoft Word MVP "ellenjones6052" <ellenjones6052@discussions.microsoft.com> wrote in message news:D869EA6F-2165-4A51-8BB6-9EBA63F436BA@microsoft.com... > On Microsoft Word 2003, I could use the find file command ...

The system could not find the volume specified 0x801F0014 DPM2010
DPM Server is 2008 r2 x64 and protected server is 2008 sp2 x64. All of a sudden this started happening. Reinstalled agent, and recreated the protection group didn't fix the issue. Type: Consistency check Status: Failed Description: An unexpected error occurred during job execution. (ID 104 Details: The system could not find the volume specified (0x801F0014)) More information End time: 3/10/2010 3:57:31 PM Start time: 3/10/2010 3:57:28 PM Time elapsed: 00:00:03 Data transferred: - Cluster node - Source details: F:\ Protection group members: 2 Details Protection gr...

Look up last entry for text boxes or combo boxes
Hello all, I'm consolidating some data that my company has accumulated and many of the peices of data carry over to the next entry many times over. In other words, many entries may have duplicate peices of data, i.e. location, country, building, etc... I would like to make it so that I can have some of my combo and text boxes remember what was last entered so I don't have to retype it every time. I thought that I had seen something like this before, but after a morning of searching and reading I have decided to post. Thanks in advance, Fletcher. This was answered earlier -- Put ...

Having trouble using 'find'
After a user's mailbox is moved from Exchange 2000 to Exchange 2003, he can no longer do use the find feature. As a test, we try to do a 'find' for emails in his inbox from someone we know he has a lot of them in his inbox. The find finished in like one second and show nothing. Please help. Kit ...

vba for find last column with hidden columns in worksheet
Hello, This code has worked for finding the last column: lc = ws.cells(7, columns.count).end(xltoleft).column. However, when I used this code for a sheet that has hidden columns it would not work. What would be the correct code in this instance? Please assist. Thanks from OffDev wannabe Hi, lc = Rows(7).Find(What:="*", SearchDirection:=xlPrevious).Column Mike "OffDev" wrote: > Hello, > > This code has worked for finding the last column: > lc = ws.cells(7, columns.count).end(xltoleft).column. > > However, when I us...

'find' command button
I have a 'find record' command button on a form. How do I make it open the 'find' popup with 'Lookin' set to the whole database (rather than the field that the client is in when they press 'find') and 'Match' to any part of the field, rather than whole field. Thanks karen The second part is easy! Goto Tools - Options - Edit/Find and set Default Find/Replace to General Search. For the first part of your question you need to clarify. Do you really mean "the whole database" opr do you simply mean to search all fields in the form? And what...

Where can I find a 'Goal Setting" template?
I am looking for a template which I can use for 'goal setting'. There are programs out there selling for $100. or more. Is there one available on excel? Have you tried Excel's Goal Seek, or for more complex solutions, Solver? Both are in the Tools Menu. Peter "msdixipeach" wrote: > I am looking for a template which I can use for 'goal setting'. There are > programs out there selling for $100. or more. Is there one available on > excel? ...

How to get last session position in 2007 Word
Hi! There was the simple and vary convenient AutoOpen macro in 2003 Word Sub AutoOpen() Application.GoBack End Sub that give last editing position when open document. This macro does not work at all in 2007 Word. What can I do instead? Check this thread. http://www.microsoft.com/office/community/en-us/default.mspx?query=application.GoBack&dg=microsoft.public.word.docmanagement&cat=en-us-office-word&lang=en&cr=US&pt=617c4d62-4061-4107-8d46-2a22fc6fa202&catlist=&dglist=&ptlist=&exp=&sloc=en-us Hope it helps, -- Brian McCaffery ...

How to find free News-Server about science?
Hi, i hope someone can help me to find free Servers with newsgroups about sciences , medicine, pharmacie and so one. I'm total new and I use Outlook to show all informations. Thanks Kerstin Ask your ISP or Google it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Kerstin Hummel asked: | Hi, | | i hope someone can help me to find free Servers with newsgroups about | sciences , medicine...

Advanced Find OR condition
How can Advanced Find be used to perform the following search: List all Contacts... - where their Job Title is "Chief Executive" OR - where a custom field on a custom related entity is "Chief Executive" Can OR conditions only be placed on fields within the same entity? This seems like a bit of a hole in an otherwise extremely powerful tool. ------=_NextPart_0001_4B88F1EF Content-Type: text/plain Content-Transfer-Encoding: 7bit "Optevia" <keithdelarge@gmail.com> wrote: > How can Advanced Find be used to perform the following search: > > List al...

Removing last exchange 5.5 from exchange 2003
We have installed exchange 2003 linked to 5.5 and move all the mail from 5.5 to 2003 my question is, now is the time to remove the last exchange 5.5 I am following the MS instruction on removing the last exchange but is not very clear I am to the point that I am replicating offline address book and schedule + free busy and eforms my problem is the everything is sync out except free busy folder and I have a x.400 MTA from exchange 5.5 listed in my 2003 queue that when I turn off the 5.5 server keep sending mesage to 5.5. Your help is greatly appreciated. Max What are you using for your ...

Last cell in a range
Can anyone tell me the way to display the latest entry into a range of cells in a downward entry e.g A1 3 A2 5 A3 6 A4 5 I would like a cell to report the last cell in the column regardless of whether it is the highest or lowest in the range. Any help is as always Appreciated. Andrew. Andrew Will return the last entry in column A no matter what type of data. =LOOKUP(2,1/(A1:A65535<>""),A1:A65535) For Numeric entry only.............. =LOOKUP(9.99999999999999E+307,A:A) For Text entry only.................... =LOOKUP(REPT("z",255),A:A) Gord Dibben MS E...

Last filled Column in a Worksheet
Hi all! Pls cud some1 tell me how to find the last filled column in a Excel worksheet? The xlCellTypeLastCell() gives the last TYPED cell,which is not always the last filled cell. Is there a way out? Anita. Anita Sometimes this is a little tricky, due to Xl's reluctance to set it's UsedRange after cells have been used and cleared. I normally use a line similar to iLastCol = Range("IV1").End(xlToLeft).Column -- HTH Nick Hodge Southampton, England nick_hodge@btinternet.com "Anita K" <mithaas22@yahoo.com> wrote in message news:07c801c39144$6e95f1b0$...

Event ID 12002 error occured while processing message
Hi, Have just upgraded to Exchange 2003 from 2000 last weekend and now am getting error 12002 800804DE against MSExchangeIS The message says error occured while processing message from (email address). All the Microsoft articles I read refer to Exchange 2000 not 2003 any help appreciated. This has happened about 5-6 times in the last week. We have no virus software installed on the Server. I made sure of that before doing the upgrade so that no issues would arise. Do we need to do anything about these messages thanks -- Kath ...

finding the size of files using vc++
I need to write code in vc++ for finding the size of any file or document that the user has clicked on. I also need to know how to compress documents and then find the size of the compressed file. Can anybody help me ASAP? > need to write code in vc++ for finding the size of any file or document that the user has clicked on. Take a look at CFileFind -------- Ajay Kalra ajaykalra@yahoo.com Use GetFileSize(). "dc" <dc@discussions.microsoft.com> wrote in message news:BC8EB795-2850-4C89-9CFC-6B5A9A76AA3E@microsoft.com... > I need to write code in vc++ for finding th...

Formula to find average of field for all rows that contain another field
Say I have a worksheet with the following information: Name Position Salary John Producer $10,000 Jeremy Producer $98,000 Jaime Producer $50,000 Darren Artist $67,000 Chris Artist $75,000 Clint Artist $30,000 Adam Artist $57,000 In Cell C2 (salary for John) I want to create a formula that looks for all rows that contain the same position as in B2 (John's position which is producer) and then calculates the average of all the salaries in column C of those rows that have the position producer. Then I want to compare that calculated average salary for all rows that have the position prod...

finding last non empty cell in a column #2
Perfect - Thank -- cparson ----------------------------------------------------------------------- cparsons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=26238 ...

Word97
I just noticed that if I load a document into Word and then try to paste something to Find or Replace form from another document (assumed string being pasted comes from clipboard) that Word 97 will Not allow you to paste that string -- It must be manually typed. Would someone who is still using Word 97 please confirm this for me. If this is by design "normal for 97" --- or if there a work around and/or registry setting to correct this? Thanks David Have you tried pasting the text into the body of the document first, then re-copying and then pasting into the Fi...

finding last value in rows
Here's what I have. In column B5 I have a list of 35 people and i columns C5 through AL5 I have weekly values, all with 0 values excep the first week which has already been entered. I need excel to find th last value that is not 0 for each of the 35 people. I tried this formula but it only seems to work with the first week o values. =INDEX('Totals sheet'!C5:AL5,COUNTA('Totals sheet'!C5:AL5)>0) Can anyone help?...thanks -- Message posted from http://www.ExcelForum.com Hi one way: enter the following array formula (CTRL+SHIFT+ENTER): =INDEX('Totals sheet'!C5:A...

How to find a text in a string without giving the position of the
My requirement are I want to find a particular text in a string with out giving the position of the text . I believe that if we use find and search we have to give the text position for the particular text. Can anyone guide me what should be the function used for getting this. Post example(s) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Sreekanth" <Sreekanth@discussions.microsoft.com> wrote in message news:B2ABF7FC-5745-4863-AA05-4EC1D61C825E@microsoft.com... > My requirement are > I want to find a particular text in...