Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
Hi
Another solution (without column AX) LastRow=Range("A2").end(xlDown).Row For Each thisRow it Range("B2",Range("AJ" & LastRow)) Regards, Per On 10 Okt., 02:49, HarryisTrying wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color sort - any cell in row that has colorindex = 4
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!!!!!!! -- Thank You "Per Jessen" wrote: Hi Another solution (without column AX) LastRow=Range("A2").end(xlDown).Row For Each thisRow it Range("B2",Range("AJ" & LastRow)) Regards, Per On 10 Okt., 02:49, HarryisTrying wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ColorIndex vs Color? | Excel Programming | |||
tab.colorindex linked to cell color | Excel Worksheet Functions | |||
ColorIndex returns incorrect color | Excel Programming | |||
Name of color for a particular colorindex number | Excel Programming | |||
need to set a cell background color which is not within the Excel colorindex range | Excel Programming |