Conditional Formating more than 3 things

[weiro]

I am trying to condional format some cells with text in so that H changes to one colour and S changes to anouther. I can do this using conditional formating but can only do three conditions.

I found this on here

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:A10")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 10
Target.Interior.ColorIndex = 5
Case 10 To 20
Target.Interior.ColorIndex = 10
Case 21 To 30
Target.Interior.ColorIndex = 6
Case 31 To 40
Target.Interior.ColorIndex = 46
Case 41 To 50
Target.Interior.ColorIndex = 45
End Select
End If
End Sub
'END OF CODE

but this is set up to work with numbers not text. Can anyone help change it or give me anouther way

 

[GlennUK]

You have only mentioned 2 conditions, H and S. You've said you want to program for more than 3, so what are all the conditions then?

 

[weiro]

It is just lots of letters. They are in fact. H, S, N, HDH and HDS but I may want to add more to the list.

Thanks

 

[GlennUK]

And what's the range to cover? The code you posted will colour cells A1:A10.

 

[weiro]

It will vary as I need to do it on differnt sheets. A1 to A10 will do for now as i can change it later.

Thanks

 

[GlennUK]

Start with this then:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim WatchRange As Range
    Dim CellVal As String
    If Target.Cells.Count > 1 Then Exit Sub
    If Target = "" Or IsNumeric(Target) Then Exit Sub
    CellVal = Target
    Set WatchRange = Range("A1:A10")
    
    If Not Intersect(Target, WatchRange) Is Nothing Then
        Select Case CellVal
        Case "H"
            Target.Interior.ColorIndex = 5
        Case "S"
            Target.Interior.ColorIndex = 10
        Case "N"
            Target.Interior.ColorIndex = 6
        Case "HDH"
            Target.Interior.ColorIndex = 46
        Case "HDS"
            Target.Interior.ColorIndex = 45
        End Select
    End If
End Sub

 

[weiro]

Sorry that seems to throw an error.

I am just copying it into the veiw code of the sheet.

Am I doing something wrong

 

[GlennUK]

And what error exactly, would that be???????

 

[weiro]

It sates Compile error then syntax error

Sorry

 

[thunderfoot]

This is also something I've been looking for, BUT if I enter a letter not in the list or leave the cell blank by, say, deleting 'H' from a cell, I would want the colour to be removed from the cell.

Also what would be the code if I wanted to make the text in the cell Bold as well.