Color sort - any cell in row that has colorindex = 4

[HarryisTrying]

I wrote a macro to turn the interior color of a cell green if the value is
changed.
I am sent a worksheet with several hundred rows (no fixed # of rows) and I
need to deal with the green cells.

Excel 2007 lets you sort by color only for one column. I want to have a
macro look at the cell color from A:AK for each row in the worksheet and if
any cell is green to sort the row to the top (or filter it to only show cells
with green). If the reen 'rows'
could be sorted by column A that would really be fantastic but not a
requirement.

I have a manual work around and have tried some VBA myself but this is over
my head.

Anyone know how to accomplish this?

 

[Patrick Molloy]

Option Explicit
Sub main()
Dim cell As Range
Dim thisRow As Range
' add a column
Range("A:A").Insert
For Each thisRow In Range(Range("B1"), Range("AJ1").End(xlDown)).Rows
Cells(thisRow.Row, 1) = "x"
For Each cell In thisRow.Cells
If cell.Interior.ColorIndex = 4 Then
Cells(thisRow.Row, 1) = "a"
Exit For
End If
Next
Next
Range("A1").CurrentRegion.Sort Range("A1")
Range("A:A").Delete
End Sub

so we add a column
(so A-AK becomes B-AJ)
set the col A value to a (so it sorts correctly later)
then for each row we loop through the cells. if one is green, set the A cell
to x then skip to the next row - there's no point in checking more cells in
the same row
once all rows are checked, sort the table by col A then remove column A

 

[HarryisTrying]

This is so much better than what I was trying but it only goes to row 25 and
then into the sort section.

Does this have something to do with the For Each thisRow In
Range(Range("B1", Range ("AJ1").End(xldown).Rows line?

I have about 900 rows and I don't think it is figuring that out. It does put
X in all rows however. That line above is a bit more complicated than I can
grasp at my level of understanding.

I have 37 columns and up to a few thousand rows on some worksheets.

Thank you so much for what you have already provided. I learn a lot this way
but still am not close to understanding all the fancy things experienced
people can do with VBA.

 

[HarryisTrying]

I am trying to troubleshoot the problem. I put a Stop (red dot) next to the
line that begins the sort statement.

When I look at the worksheet x are down through row 195 (tried this many
times).

I added this code to see how many rows there are

Range("A2").Select
Selection.End(xldown).Select
intRowEnd = ActiveCell.Row

when I step through it I get intRowEnd = 826

 

[HarryisTrying]

Well,

If I create a new simple worksheet with a few columns and radomly make the
colorindex = 4 in some cells it works perfectly.

But when I use it with the data from my worksheet it doesn't work. Goes down
to around row 195 or a bit further and stops putting X in column A

I copied the data into a new worksheet using paste special and choosing
vlaues. I thought it might be a formula or another macro but this is a new
workbook with only the code in sheet1

 

[HarryisTrying]

Yikes,
It was really not understanding that one line. I should have mentioned that
column A is the only column that has to have a value in it. It was looking in
AJ and found a empty cell and stop.

I made a crude work around by coping column A to Column AX and modifying the
line to read as follows
' I added two lines below
Range("A:A").Copy
Range("AW:AW").PasteSpecial ' something else goes here because it paste to AX
' add a column
Range("A:A").Insert

'modified range to be Range("AX2)...
' also modified to start on row 2 because row 1 is headers
For Each thisRow In Range(Range("B2"), Range("AX2").End(xlDown)).Rows

I think I learned a bit but sure was slow on figuring it out.

 

[Per Jessen]

Hi

Another solution (without column AX)

LastRow=Range("A2").end(xlDown).Row
For Each thisRow it Range("B2",Range("AJ" & LastRow))

Regards,
Per

 

[HarryisTrying]

Thanks to Per and Patrick for your valuable help. I got this working and
learned a lot too! I am a novice at this, so I hope it is ok to ask these
novice level questions. Everyone else seems so smart!

Oh, I added some lines to delete all rows that were not changed and then
sort the remaining rows alphabetically based on Row A (after the temp row A
is deleted).

Now that I have the changes I was going to manually update the Master file
(same exact layout) by hand. But I suppose a macro could be written to match
column A (unique names) in the recieved file to the same name in the Master
file and then find the green cells in the file received file and go and
change the same cells in the master.

That way a macro could do it all if I put the recieved file in one folder
and the master in another on my C dirve.

Wow! if that can be pulled off I may get so exicted I will shout for
joy!!!!!!!