This macro identifies specifc codes in column A and changes the colour of the cell accordingingly.
Can someone give me a macro that will run down column A starting at A6 and edit the cell and move on down to A160 so that all of the formats are correct?
Many thanks
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, bc As Long
Set d = Intersect(Range("A6:A160"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "YNPH"
bc = 22
Case "WFBC"
bc = 40
Case "WFT"
bc = 6
Case "WFA"
bc = 37
Case "WF"
bc = 35
Case "AFCC" '
bc = 28
Case "WFBQ"
bc = 19
Case "WFB"
bc = 26
Case "WFJ"
bc = 17
Case "WFTM"
bc = 24
Case "AFF" '
bc = 22
Case "AFC" '
bc = 50
Case "AFCL"
bc = 47
Case "WFU"
bc = 46
Case "WFBS"
bc = 45
Case "AFKG"
bc = 54
Case "WBVS"
bc = 43
Case "WFL"
bc = 42
Case Else
fc = 1: fb = False: bc = xlNone
End Select
With Cells(c.Row, 1).Resize(, 13)
.Interior.ColorIndex = bc
End With
Next
End Sub
Bookmarks