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?
--
Thank You

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" wrote:

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?
--
Thank You

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.
--
Thank You


"Patrick Molloy" wrote:

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" wrote:

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?
--
Thank You

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


--
Thank You


"HarryisTrying" wrote:

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.
--
Thank You


"Patrick Molloy" wrote:

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" wrote:

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?
--
Thank You

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
--
Thank You


"HarryisTrying" wrote:

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


--
Thank You


"HarryisTrying" wrote:

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.
--
Thank You


"Patrick Molloy" wrote:

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" wrote:

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?
--
Thank You

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.
--
Thank You


"HarryisTrying" wrote:

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
--
Thank You


"HarryisTrying" wrote:

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


--
Thank You


"HarryisTrying" wrote:

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.
--
Thank You


"Patrick Molloy" wrote:

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" wrote:

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?
--
Thank You