LinkBack URL
About LinkBacks
I have a spreadsheet where the color of the cells in column A represents different stages. I want to distribute this sheet as a report of all but the last stage. Can anyone help?
I have a spreadsheet where the color of the cells in column A represents different stages. I want to distribute this sheet as a report of all but the last stage. Can anyone help?
Provide an example wb please
sample attached
What color stands for last stage?
light blue. Thanks
Got it, will put the code below.
First you must know that the light blue used has several different colors.
Use one color for that.
If you need the color from a cell select the cell and run next macro
Please Login or Register to view this content.
Please Login or Register to view this content.
Hi Gerard,
Im just digging myself from up under our month end closing process to take a look at this. I picked one blue shade of highlight and ran the macro. A few of the blue rows were deleted and a few were not. I ran the macro a second time and the remaining blues were deleted. Do you know why I have to run the macro twice? A copy of the macro is below and a sample spreadsheet is attached.
Thanks so much for your help. This saves me a great amount of time.
Sub deleteRowOnCollor()
Dim lLR, lC As Long
lLR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
For lC = 3 To lLR
Sheets(1).Cells(lC, 1).Activate
If ActiveCell.Interior.Color = 15773696 Then 'Used index color.
Rows(lC).Delete Shift:=xlUp 'delete row
'Rows(lC).EntireRow.Hidden = True 'Hide row.
End If
Next
End Sub
Ha tricked my self with this one....
Because the code ran from top to bottom, in case of a match the row was deleted. This caused that the next row became active row, but the counter was increase so that row would be skipped....
Solved it by running the check from bottom to the top of the page....
Also added freezing screen updating during running of macro.
Please Login or Register to view this content.
Cindy,
Try the attached.
Hi, Cindy,
Your post #7 does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Ciao,
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Gerard,
Perfect! Thanks so much for your help.
Sorry for "breaking the rules". It was not intentional. I dont post often to this forum and am not aware of all the rules. Will be more careful next time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks