Changing Cell Colour

[bubba06]

Hi - I have a drop down box with 3 entries in it - 1. Not Started 2. Work In Progress 3. Completed - if i selected "not started" I want that same cell to change to red with "not started" - Orange for "work in progress" and green for "completed" - and if completed is selected i need to make the next cell come up with date (now)

Thanks in Advance

 

[jmthompson]

Which column contains your dropdown? Which column should have the date?

 

[jmthompson]

Here is some automated code, right click on your tab, select View code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim myDate As Date
Dim Response As String
If Intersect(Range("H:H"), Target) Is Nothing Then Exit Sub
myDate = Format(Now(), "mm/dd/yyyy")

If Target.Value = "Completed" Then
Target.Offset(, 1).Value = myDate

ElseIf Target.Value <> "" And Target.Value <> "Completed" And Target.Offset(, 1).Value <> "" Then
    Response = MsgBox("If did not intend to change the status from ""Completed"", please select ""Cancel"".", vbOKCancel)
        Select Case Response
            Case Is = 1
            Target.Offset(, 1).Value = ""
    Application.EnableEvents = True
            Case Is = 2
            Target.Value = "Completed"
            
        End Select

End If
Application.EnableEvents = True
End Sub

I've based this code on your drop-down in Column H and your completed date in I.