Change cell color when selection is made from a drop down list

Catalin.B · #11 08-28-2012, 07:30 AM

I have to admit that i was wrong when i said that a change event does not respond to a Data Validation change ; the code will work every time you make a selection in column D or you clear the cell; the code you needed is presented below and must be pasted in Sheet 1 module. Sorry, my mistake !

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
        
        Set rng = Target.Parent.Range("D1:D350")
             
            If Target.Count > 1 Then Exit Sub
            
            If Intersect(Target, rng) Is Nothing Then Exit Sub
            
            With Target

    SearchArr = Array("BLACK", "BLUE", "CADIAC ALERT", "GREEN", "GREY", "ICE ALERT", "ORANGE" _
       , "PEDS CODE BLUE", "PINK", "PURPLE", "RAPID RESPONSE", "RED", "STEMI ALERT", "STROKE ALERT" _
       , "WHITE", "YELLOW")
    FontColorArr = Array("2", "2", "2", "1", "1", "1", "1", "1" _
       , "2", "2", "1", "2", "2", "1", "1", "1")
    InteriorColorArr = Array("1", "5", "30", "4", "16", "37", "46", "8" _
       , "7", "13", "20", "3", "53", "22", "2", "6")
           For i = LBound(SearchArr) To UBound(SearchArr)
If Len(Target) = 0 Then Target.Interior.ColorIndex = 2: Target.Font.ColorIndex = 1
        If InStr(1, Target.Text, SearchArr(i), vbTextCompare) > 0 Then
        .Interior.ColorIndex = InteriorColorArr(i)
        .Font.ColorIndex = FontColorArr(i)
        End If
           Next i

    End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro ? - Checkbox in Word + drop down selection help	dice1976	Word	4	07-24-2012 11:18 AM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM
Made a mistake with a macro change	lance_kidd	Word	0	02-09-2011 06:36 PM
Ho to perform multi selection in drop down lists?	nashville	Word	0	09-29-2010 07:10 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM