Here is my current code. Appreciate your help.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Dim Rng1 As Range
Dim Rng2 As Range
Set Rng1 = Intersect(Target, Me.Range("E:E"))
Set Rng2 = Intersect(Target, Me.Range("C:C", "D
"))
On Error GoTo ws_exit
'do the column E stuff
If Not (Rng1 Is Nothing) Then
For Each rCell In Rng1.Cells
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(23, 178, 233)
Case 60
nColor = RGB(245, 200, 11)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = RGB(255, 255, 255)
End Select
If Not nColor = -1 Then
rCell.Offset(0, -4).Interior.Color = nColor
Else
rCell.Offset(0, -4).Interior.ColorIndex = _
xlColorIndexNone
End If
Next rCell
End If
'do the column B stuff
If Not (Rng2 Is Nothing) Then
Application.EnableEvents = False
For Each rCell In Rng2.Cells
With rCell
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
Next rCell
End If
ws_exit:
Application.EnableEvents = True
End Sub