Dropdown menu only for conditional formatting

[BramDingemanse]

Hello Everyone,

I know it's possible to have conditional formatting with a dropdown menu. Now I want this without taking over the value of the drop down menu.
For example:
Cell A3 contains the value "Project 1".
Also cell A3 has a dropdown menu with three options: "Not stared", "In progress", "Done".

When I select "Done" from the drop down menu I want cell A3 to turn green but not change value to "Done".
The dropdown menu only changes the color of the cell, not its value.

I know I can use another cell for the drop down menu, but I would like to have it in the same cell.


Thanks in advance!

 

[Yongle]

Welcome to the forum
You could use VBA to achieve this

Does this requirement apply to only one cell in this worksheet or to a range of cells? (what is the range?)
Does this requirement apply to one sheet or multiple sheets (is it cell A3 in every sheet?)

 

[mumps]

Copy and paste this code into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the code into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell A3.

Dim val As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
    val = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo errHandler
    If Target = "Done" Then
        With Target
            .Interior.ColorIndex = 4
            .Value = val
        End With
    Else
        Target.Interior.ColorIndex = xlNone
    End If
errHandler:
    Application.EnableEvents = True
End Sub

 

[BramDingemanse]

Hey Yongle, thanks!

This requirement is for almost the whole worksheet. I import a map structure from windows explorer with 3 levels:Supplier, Directory, Series. Those are the columns D to F. The amount of rows goes beyond 2300.
It is only one sheet.

 

[BramDingemanse]

Thank you mumps,

To get a range, I just change the A3 reference to a range?
To get the other two options as well, do I add another two If-statements? (Not started: red. In progress: orange).

 

[BramDingemanse]

Update: Your codes works. But not fully. It makes the cell green when I select done and it doesn't show the value "Done". But when I enter any other value the color disappears.

 

[kvsrinivasamurthy]

Worksheet events are used. Copy this code to Worksheet Events. Range used is A3:A5, change as required.

Public Str As String

Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


If Not Intersect(Target, Range("A3:A5")) Is Nothing Then


With Target.Interior


If Target = "Not started" Then
.Color = 255
ElseIf Target = "In progress" Then
.Color = 65535
ElseIf Target = "Done" Then
.Color = 5296274
End If


End With
Selection.Validation.Delete


Target.Value = Str


End If
Application.EnableEvents = True


End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Range("A3:A5")) Is Nothing Then
Str = Target.Value


    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Not started,In progress,Done"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If


End Sub

 

[mumps]

Is the drop down list only in A3 or is it in multiple cells? If in multiple cells, what are the cells? You said:

When I select "Done" from the drop down menu I want cell A3 to turn green but not change value to "Done".

Do you want the same thing to happen with "Not started" and "In progress" just using red and orange? Do you want the cell value to change to "Not started" and "In progress"?

 

[BramDingemanse]

Yes the same with not started and in progress, please. No I don't want the value to change to not started and in progress. I want to enter a value like "Project 1". Then select "Not started, "In progress" or "Done" to change the color of the Project 1 cell and keep the value "Project 1".

 

[kvsrinivasamurthy]

Please see my previous post. It satisfies all conditions.