VBA and dropdown list: change background color relating the user's choice

[Nelson78]

Hello everybody.

I've set a dropdown list this way:

Dim MyList(2) As String
MyList(0) = "YES'"
MyList(1) = "NO"

Dim lrut As Long
    lrut = sheet1.Cells(Rows.Count, "E").End(xlUp).Row

With Range("G2:G" & lrut).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With

I need a change in the color background of the cell in case of choice "YES".

How can I manage the matter?

 

[mumps]

Try:

Sub tetingcopy()
    Dim MyList(2) As String
    MyList(0) = "YES"
    MyList(1) = "NO"
    Dim lrut As Long
        lrut = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
    With Range("G2:G" & lrut).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(MyList, ",")
    End With
    Range("G2:G" & lrut).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""YES"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[Nelson78]

Try:

Sub tetingcopy()
    Dim MyList(2) As String
    MyList(0) = "YES"
    MyList(1) = "NO"
    Dim lrut As Long
        lrut = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
    With Range("G2:G" & lrut).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(MyList, ",")
    End With
    Range("G2:G" & lrut).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""YES"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Yes, it works absolutely fine.

Anyway, I'm a bit perplexed by the use of

Select

and

Selection

, that usually I avoid.

What do you think about?

 

[mumps]

Generally, you don't have to select a range to perform an action on it. I took the lazy way out and recorded a macro to do the conditional formatting. This revised macro does the same thing without the use of "Select".

Sub tetingcopy()
    Dim MyList(2) As String
    MyList(0) = "YES"
    MyList(1) = "NO"
    Dim lrut As Long
        lrut = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
    With Range("G2:G" & lrut).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(MyList, ",")
    End With
    With Range("G2:G" & lrut)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""YES"""
        .FormatConditions(Range("G2:G" & lrut).FormatConditions.Count).SetFirstPriority
    End With
    With Range("G2:G" & lrut).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Range("G2:G" & lrut).FormatConditions(1).StopIfTrue = False
End Sub

 

[Nelson78]

Generally, you don't have to select a range to perform an action on it. I took the lazy way out and recorded a macro to do the conditional formatting. This revised macro does the same thing without the use of "Select".

Sub tetingcopy()
    Dim MyList(2) As String
    MyList(0) = "YES"
    MyList(1) = "NO"
    Dim lrut As Long
        lrut = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row
    With Range("G2:G" & lrut).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(MyList, ",")
    End With
    With Range("G2:G" & lrut)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""YES"""
        .FormatConditions(Range("G2:G" & lrut).FormatConditions.Count).SetFirstPriority
    End With
    With Range("G2:G" & lrut).FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Range("G2:G" & lrut).FormatConditions(1).StopIfTrue = False
End Sub

Well, the same tweak I'd have done.

Thank's a lot.

 

[mumps]

You are very welcome.