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?