need to change cell colour based on text

[kit_kat328]

Hi all,

I'm a complete newbie and when it comes to VBA, a pleb as well.

What I'm trying to do is get the cell to change background colour depending on what you choose from the drop down menu.

C2 - C300

drop down menu choice:

one = colour 53
two = colour 32
three = colour 42
four = colour 3
five = colour 43
six = colour 25
seven = colour 7
eight = colour 45

can anyone give me a hand? I've been trying to get an understanding of the basics for a couple days now, and just can't get a grasp on it

 

[kit_kat328]

will this work for all worksheets in the file?

 

[AlphaFrog]

will this work for all worksheets in the file?

As it is now, No. It only works on the one worksheet. You can easily make it work for all sheets in the workbook though. Move all the code from the worksheet module to the ThisWorkbook module and make the changes in red...

[COLOR="Red"]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)[/COLOR]
    
    Dim cell As Range
    
    If Not Intersect([COLOR="Red"]Sh.[/COLOR]Range("C2:C300"), Target) Is Nothing Then
    
        Application.EnableEvents = False
        For Each cell In Intersect([COLOR="Red"]Sh.[/COLOR]Range("C2:C300"), Target)
            With cell.Interior
            Select Case LCase(cell.Value)
                Case "one": .ColorIndex = 53
                Case "two": .ColorIndex = 32
                Case "three": .ColorIndex = 42
                Case "four": .ColorIndex = 3
                Case "five": .ColorIndex = 43
                Case "six": .ColorIndex = 25
                Case "seven": .ColorIndex = 7
                Case "eight": .ColorIndex = 45
                Case Else: .ColorIndex = xlNone
            End Select
            End With
        Next cell
        Application.EnableEvents = True
        
    End If

End Sub

Ax