VBA Replace colour dependent on drop down value

Question

I have been working on a macro which populates certain cells with colors based upon drop down values. When selecting a value and hen changing it, it does not remove the previous colors that were last populated based on its value. The Macro runs on a private sub when there is a work sheet change. Which in turn checks the cell value and populates fields with colors. Would a private sub worth using when something changes to reset the active row? Here is the macro code to color in the fields:

 Sub Validate()


Dim rng As Range
Dim row As Range
Dim cell As Range
Dim counter As Long
Dim clrGrren As Long
Dim clrWhite As Long
clrGreen = RGB(Red:=180, Green:=236, Blue:=180)
clrWhite = RGB(Red:=255, Green:=255, Blue:=255)

Set rng = Range("D4:D1000")



For Each cell In rng


Select Case cell.Value

Case Is = "Action Figures"
    cell.Offset(counter, 12).Interior.Color = clrGreen
    cell.Offset(counter, 13).Interior.Color = clrGreen
    cell.Offset(counter, 21).Interior.Color = clrGreen
    cell.Offset(counter, 22).Interior.Color = clrGreen
    cell.Offset(counter, 23).Interior.Color = clrGreen
    cell.Offset(counter, 29).Interior.ColorIndex = 16
    cell.Offset(counter, 30).Interior.ColorIndex = 16
    cell.Offset(counter, 31).Interior.Color = clrGreen
    cell.Offset(counter, 32).Interior.Color = clrGreen
    cell.Offset(counter, 34).Interior.ColorIndex = 16
    cell.Offset(counter, 35).Interior.Color = clrGreen
    cell.Offset(counter, 38).Interior.ColorIndex = 16
    cell.Offset(counter, 39).Interior.ColorIndex = 16
    cell.Offset(counter, 41).Interior.ColorIndex = 16
    cell.Offset(counter, 42).Interior.ColorIndex = 16
    cell.Offset(counter, 43).Interior.ColorIndex = 16
    cell.Offset(counter, 44).Interior.ColorIndex = 16

Is the code complete? I can't see the 'End Sub'. Please provide the entire 'sub'. — Zeeshan Siddiqui
The entire sub is about 1000 lines long. The code is repeated for different scenarios so at the end of the sub it closes of with: End Select Next End Sub — Nathan
I couldn't get the solution to work, the procedure was too large — Nathan

Zeeshan Siddiqui Zeeshan Siddiqui · Accepted Answer · 2015-08-20T09:07:52

You should include the code that checks the cell value and updates the corresponding cell color in the Private Sub Worksheet_Change() instead of a user-defined sub. Save the sheet and then try updating the values. The color of the targeted cells will change automatically.

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim counter As Long
Dim clrGreen As Long
Dim clrWhite As Long
Dim clrBlue As Long

    Private Sub Worksheet_Change(ByVal cell As Range)
    clrGreen = RGB(Red:=180, Green:=236, Blue:=180)
    clrWhite = RGB(Red:=255, Green:=255, Blue:=255)
    clrBlue = RGB(Red:=0, Green:=0, Blue:=255)

    Select Case cell.Value

    Case Is = "Action Figures"
        cell.Offset(counter, 12).Interior.Color = clrGreen
        cell.Offset(counter, 13).Interior.Color = clrGreen
        cell.Offset(counter, 21).Interior.Color = clrGreen
        cell.Offset(counter, 22).Interior.Color = clrGreen
        cell.Offset(counter, 23).Interior.Color = clrGreen
        cell.Offset(counter, 29).Interior.ColorIndex = 16
        cell.Offset(counter, 30).Interior.ColorIndex = 16
        cell.Offset(counter, 31).Interior.Color = clrGreen
        cell.Offset(counter, 32).Interior.Color = clrGreen
        cell.Offset(counter, 34).Interior.ColorIndex = 16
        cell.Offset(counter, 35).Interior.Color = clrGreen
        cell.Offset(counter, 38).Interior.ColorIndex = 16
        cell.Offset(counter, 39).Interior.ColorIndex = 16
        cell.Offset(counter, 41).Interior.ColorIndex = 16
        cell.Offset(counter, 42).Interior.ColorIndex = 16
        cell.Offset(counter, 43).Interior.ColorIndex = 16
        cell.Offset(counter, 44).Interior.ColorIndex = 16

    Case Is = "Dolls"
        cell.Offset(counter, 12).Interior.Color = clrBlue
        cell.Offset(counter, 13).Interior.Color = clrBlue
        cell.Offset(counter, 21).Interior.Color = clrBlue
        cell.Offset(counter, 22).Interior.Color = clrBlue
        cell.Offset(counter, 23).Interior.Color = clrBlue
        cell.Offset(counter, 29).Interior.ColorIndex = 16
        cell.Offset(counter, 30).Interior.ColorIndex = 16
        cell.Offset(counter, 31).Interior.Color = clrBlue
        cell.Offset(counter, 32).Interior.Color = clrBlue
        cell.Offset(counter, 34).Interior.ColorIndex = 16
        cell.Offset(counter, 35).Interior.Color = clrBlue
        cell.Offset(counter, 38).Interior.ColorIndex = 16
        cell.Offset(counter, 39).Interior.ColorIndex = 16
        cell.Offset(counter, 41).Interior.ColorIndex = 16
        cell.Offset(counter, 42).Interior.ColorIndex = 16
        cell.Offset(counter, 43).Interior.ColorIndex = 16
        cell.Offset(counter, 44).Interior.ColorIndex = 16
    End Select

End Sub

VBA Replace colour dependent on drop down value

1 Answers