Help needed in VBA code

[spreethi81]

Hi Experts,

I need to change the colour of the cells depending on the value in the cell.
Condn 1: If value is "Start", colour of cell must change to "Red",
Condn 2: If value is "In Progress" or "In Progress A" or "In Progress B" or "In Progress C" , colour of cell must be "Yellow" and
Condn 3: if value is "End" or "Over", colour of cell must be "Green".


If more words starting with "In Progress" would be added, how do i modify the macro which is given below?

Private Sub Worksheet_Activate()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A1:A3000")

For Each rCell In rng

If rCell.Value = "start" Then
rCell.Interior.Color = &HC080FF
End If

If rCell.Value = "in progress" Then
rCell.Interior.Color = &H7AEAEF
ElseIf rCell.Value = "in progress a" Then
rCell.Interior.Color = &H7AEAEF
ElseIf rCell.Value = "in progress b" Then
rCell.Interior.Color = &H7AEAEF
ElseIf rCell.Value = "in progress c" Then
rCell.Interior.Color = &H7AEAEF
End If

If rCell.Value = "done" Then
rCell.Interior.Color = &HE9FBA2
ElseIf rCell.Value = "over" Then
rCell.Interior.Color = &HE9FBA2
ElseIf rCell.Value = "end" Then
rCell.Interior.Color = &HE9FBA2
End If
Next
End Sub

Thanks in advance,
Preethi.

[rylo]

Hi

Try

Private Sub Worksheet_Activate()
Dim rng As Range
Dim rCell As Range

Set rng = Range("A1:A3000")

For Each rCell In rng

If rCell.Value = "start" Then
rCell.Interior.Color = &HC080FF
End If

'If rCell.Value = "in progress" Then
'rCell.Interior.Color = &H7AEAEF
'ElseIf rCell.Value = "in progress a" Then
'rCell.Interior.Color = &H7AEAEF
'ElseIf rCell.Value = "in progress b" Then
'rCell.Interior.Color = &H7AEAEF
'ElseIf rCell.Value = "in progress c" Then
'rCell.Interior.Color = &H7AEAEF
'End If
If rCell.Value Like "in progress*" Then rCell.Interior.Color = &H7AEAEF

If rCell.Value = "done" Then
rCell.Interior.Color = &HE9FBA2
ElseIf rCell.Value = "over" Then
rCell.Interior.Color = &HE9FBA2
ElseIf rCell.Value = "end" Then
rCell.Interior.Color = &HE9FBA2
End If
Next
End Sub

rylo

[spreethi81]

Hi rylo,

I tried using

If rCell.Value Like "in progress*" Then
rCell.Interior.Color = &H7AEAEF
End If

but, any word starting with "i" also gets converted to yellow colour.Its not working correctly.

Thanks & Regards,
Preethi.

[spreethi81]

Hi Rylo,

I tried it once again and it worked!!! Thanks a million!!!

Warm Regards,
Preethi.