Insert text based on another cell's colour or font in Excel 2003
eI have a table of data (b2:f600). Some of the rows are green, some yellow,
etc. I need a formula/macro that will put a number or text in column A based on the row's colour (eg if b2 is green then put "1" or "green" in a2). The coloured rows are not in consecutive order. I use Excel 2003. Anyone? |
Insert text based on another cell's colour or font in Excel 2003
hi
see this site for xl color indexes... http://www.mvps.org/dmcritchie/excel/colors.htm Sub testit() Dim r As Range Dim rd As Range Set r = Range("B1") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) r.Select If r.Interior.ColorIndex = 4 Then 'green r.Offset(0, -1).Value = 1 'or A Else If r.Interior.ColorIndex = 6 Then 'yellow r.Offset(0, -1).Value = 2 'or b End If End If Set r = rd Loop MsgBox ("Done!") End Sub regards FSt1 "Philip Hinton" wrote: eI have a table of data (b2:f600). Some of the rows are green, some yellow, etc. I need a formula/macro that will put a number or text in column A based on the row's colour (eg if b2 is green then put "1" or "green" in a2). The coloured rows are not in consecutive order. I use Excel 2003. Anyone? |
Insert text based on another cell's colour or font in Excel 20
hi
remove r.select; i just added that as part of the test. no need to select anything. sorry. regards FSt1 "FSt1" wrote: hi see this site for xl color indexes... http://www.mvps.org/dmcritchie/excel/colors.htm Sub testit() Dim r As Range Dim rd As Range Set r = Range("B1") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) r.Select If r.Interior.ColorIndex = 4 Then 'green r.Offset(0, -1).Value = 1 'or A Else If r.Interior.ColorIndex = 6 Then 'yellow r.Offset(0, -1).Value = 2 'or b End If End If Set r = rd Loop MsgBox ("Done!") End Sub regards FSt1 "Philip Hinton" wrote: eI have a table of data (b2:f600). Some of the rows are green, some yellow, etc. I need a formula/macro that will put a number or text in column A based on the row's colour (eg if b2 is green then put "1" or "green" in a2). The coloured rows are not in consecutive order. I use Excel 2003. Anyone? |
Insert text based on another cell's colour or font in Excel 20
Thanks for the input FSt1. Your code works but only if the rows are coloured
"yellow" or "bright green" (as Excel sees them). I need something that will give a result for ANY colour. However, I followed up on your dmcritchie link and found http://www.cpearson.com/excel/SortByColor.htm, which does it for me. Thanks for the pointers. Philip "FSt1" wrote: hi see this site for xl color indexes... http://www.mvps.org/dmcritchie/excel/colors.htm Sub testit() Dim r As Range Dim rd As Range Set r = Range("B1") Do While Not IsEmpty(r) Set rd = r.Offset(1, 0) r.Select If r.Interior.ColorIndex = 4 Then 'green r.Offset(0, -1).Value = 1 'or A Else If r.Interior.ColorIndex = 6 Then 'yellow r.Offset(0, -1).Value = 2 'or b End If End If Set r = rd Loop MsgBox ("Done!") End Sub regards FSt1 "Philip Hinton" wrote: eI have a table of data (b2:f600). Some of the rows are green, some yellow, etc. I need a formula/macro that will put a number or text in column A based on the row's colour (eg if b2 is green then put "1" or "green" in a2). The coloured rows are not in consecutive order. I use Excel 2003. Anyone? |
All times are GMT +1. The time now is 06:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com