Excel VBA Macro

Wompa1

Hi Guys,

I'm trying to get a Macro which I thought sounded easy. I have a list containing Red, Green and Amber...if a user select Green the Cell should change to Green fill.

What I have below will only assign the colour when I type Red, Green or Amber and then click another cell.

Or if I choose one of the colours from my list. Then select another one from the one below, delete it and click another cell. Its really frustrating.

What I want is if the user selects from the list Red, Green or Amber that the colour change, Don't want them to have type it in. Any Suggestions?

Thanks in advance

Sub auto_open()

' Run the macro DidCellsChange any time a entry is made in a
' cell in Sheet1.
ThisWorkbook.Worksheets("Overall RAG").OnEntry = "DidCellsChange"

End Sub


Sub DidCellsChange()
Dim KeyCells As String
' Define which cells should trigger the KeyCellsChanged macro.
KeyCells = "A1:A1000"

' If the Activecell is one of the key cells, call the
' KeyCellsChanged macro.
If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then KeyCellsChanged

End Sub

Sub KeyCellsChanged()
Dim Cell As Object
' If the values in A11:C11 are greater than 50...
For Each Cell In Range("A2:A1000")
If Cell = "Red" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 3

End If


If Cell = "Green" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 4

End If


If Cell = "Amber" Then

' Make the background color of the cell the 3rd color on the
' current palette.
Cell.Interior.ColorIndex = 6

End If
Next Cell

End Sub

Wompa1

This can be closed. I discovered in Office 2007 I could use Conditional Formatting on my column. Thanks Anyways Guys. You can close this please Mr. Mod!