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

 

[AlphaFrog]

• What version of Excel do you have?
• What cell(s), have the drop-down lists?
• Do you want to change all cells in C2:C300 based on one drop-down list or do each of the cells in C2:C300 have the drop-down lists?

 

[kit_kat328]

• What version of Excel do you have?
• What cell(s), have the drop-down lists?
• Do you want to change all cells in C2:C300 based on one drop-down list or do each of the cells in C2:C300 have the drop-down lists?

I'm using excel 2007, but I need this to work on 2002. Each cell from C2-C300 have a drop down menu each and I want the cell to change colour based on what you choose from the drop down menu.

 

[AlphaFrog]

• Right-clcick on the sheet tab that has the drop-down lists
• Select from the pop-up menu View Code
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    If Not Intersect(Range("C2:C300"), Target) Is Nothing Then
    
        Application.EnableEvents = False
        For Each cell In Intersect(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

 

[kit_kat328]

it didn't work. copied it as you said

 

[kit_kat328]

do i do anything with the 2 drop down menus at the top of the vba edit menu?
they are currently set to Worksheet and Change

 

[kit_kat328]

nevermind. Got it working. Your an absolute gem!!!

how do you show appreciation on here?

 

[AlphaFrog]

do i do anything with the 2 drop down menus at the top of the vba edit menu?
they are currently set to Worksheet and Change

The two VBA drop-downs should say Worksheet and Change

I'm guessing you may have previously disabled the Application.Events when you were writing your own VBA code. If yes, you need to re-enable the events so that this macro automatically triggers when you make a change to the worksheet.

Paste this macro in a standard module (not in the ThisWorkbook module) and run it.

Sub ReEnable_Events()
Application.EnableEvents = True
MsgBox "Events are enabled."
End Sub

You only need to run this macro this one time to reset your current session in Excel.

 

[AlphaFrog]

nevermind. Got it working. Your an absolute gem!!!

how do you show appreciation on here?

Just a simple Thank you. I'm glad it works for you. You're welcome.

Was it the Application.EnableEvents thing?

 

[kit_kat328]

no...it was me being a clumsy fool and put a decimal in the code by accident. I really do appreciate your help with this. You must get tons of muppets like me in here...lol