Results 1 to 6 of 6

Thread: VBA and dropdown list: change background color relating the user's choice
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    382
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    Hello everybody.

    I've set a dropdown list this way:


    Code:
    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?

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,245
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    Try:
    Code:
    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
    Last edited by mumps; Oct 18th, 2018 at 09:55 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    382
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mumps View Post
    Try:
    Code:
    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
    Code:
    Select
    and
    Code:
    Selection
    , that usually I avoid.

    What do you think about?

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,245
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    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".
    Code:
    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    Sep 2017
    Posts
    382
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by mumps View Post
    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".
    Code:
    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.

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,245
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    5 Thread(s)

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

    You are very welcome.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •