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?

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?

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?

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?