I would like to change the Cell.Interior.Color based on the text in the cell

[ErinJ]

I need each cell in Range E18:BL18 to change color based on the letter in the cell. This is what I was thinking but it's not working:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("E18:BL18").Value
Case Is = "M"
Cell.Interior.Color = vbBlue
Case Is = "C"
Cell.Interior.Color = vbRed
Case Else
Cell.Interior.Color = vbWhite
End Select

End Sub

Please help

 

[JLGWhiz]

I need each cell in Range E18:BL18 to change color based on the letter in the cell. This is what I was thinking but it's not working:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("E18:BL18").Value
Case Is = "M"
Cell.Interior.Color = vbBlue
Case Is = "C"
Cell.Interior.Color = vbRed
Case Else
Cell.Interior.Color = vbWhite
End Select

End Sub

Please help

Have you tried conditional formatting? You don't really need VBA.

 

[ErinJ]

Conditional formating works good but I'm needing to add more variables that I can't do in conditional formating

 

[ErinJ]

I got this to kinda work but if the cell value changes it doesn't change with it.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each Cell In Range("E18:BL18")
If Cell.Value = "M" Then _
Cell.Interior.Color = vbBlue
If Cell.Value = "C" Then _
Cell.Interior.Color = vbRed
If Cell.Value = " " Then _
Cell.Interior.Color = vbWhite
Next Cell
End Sub

 

[ErinJ]

Nevermind this works! I just had a typo. But if anyone can tell me a better way to write this I'd be happy to learn

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Range("E18:BL18")
If Cell.Value = "M" Then _
Cell.Interior.Color = vbBlue
If Cell.Value = "C" Then _
Cell.Interior.Color = vbRed
If Cell.Value = "" Then _
Cell.Interior.Color = vbBlack
Next
End Sub

I'm using it in conjuncture with:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("B18").Value
Case Is = "Manager"
Range("E18:BL18").Value = "M"
Case Is = "Cook"
Range("E18:BL18").Value = "C"
Case Is = "Prep"
Range("E18:BL18").Value = "K"
Case Is = "Packer"
Range("E18:BL18").Value = "P"
Case Is = "Cashier"
Range("E18:BL18").Value = "C"
Case Else
Range("E18:BL18").Value = " "
End Select
End Sub

 

[JLGWhiz]

This Might save you some grief by preventing the changes unless the target cell that is changed is in E15:BL18 range.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Taget, Range("E18:BL18") Is Nothing Then
For Each Cell In Range("E18:BL18")
If Cell.Value = "M" Then _
Cell.Interior.Color = vbBlue
If Cell.Value = "C" Then _
Cell.Interior.Color = vbRed
If Cell.Value = "" Then _
Cell.Interior.Color = vbBlack
Next
End If
End Sub
Code:

 

[ErinJ]

Thanks JLGWhiz for your help.