Help Evaluating Cell Values and Changing Colors

[ChrisG]

I have a column B1 to B? with one of a possible 5 string
values in each cell. I wish to evaluate the value in the
B-cell and depending on that value is then change the
color of font in the corresponding cell in column C.

Note: B? means Column B is of variable length, could be
30 and next day could be 40.

Thanks in advance

 

[Tom Ogilvy]

set rng = Range(Cells(1,2),Cells(rows.count,2).End(xlup))

for each cell in rng
Select Case Cell.Value
Case "string1"
cell.offset(0,1).Font.colorindex = 2
Case "string2"
cell.offset(0,1).Font.Colorindex = 5
. . .
Case Else
cell.Offset(0,1).Font.Colorindex = xlAutomatic
End Select
Next

make sure the case of your strings match or do

Select case lcase(cell.Value)

and have the constant strings as all lowercase.


Regards,
Tom Ogilvy