Using Data Validation in conjunction with VBA

[Felix_Dragonhammer]

I have a workbook that will be used to help with process of evaluating which products will be carried over into next year.
There is a drop down menu with "Yes" or "No" as answers in B2 that is currently set at "Yes". I also have another series of cells with dropdowns that acts like a checklist. Once one step is completed, you click "Yes". Naturally, if the product is not going to be carried over into the next year, all the steps on the checklist are irrelevant.

What I would like to do is whenever the cell in the "Go Forward?" column is checked to "No" is fill every cell in that row with the color Red: 150, Green; 54 and Blue: 52 and to replace every list in that row marked with an asterisk with the value "No" (i.e. no data validation list).

Any help achieving this would be appreciated.

Here is an example of my current worksheet.

[TABLE="width: 3935"]
<tbody>[TR]
[TD]Full Model Number 2015
[/TD]
[TD]Go Forward?
[/TD]
[TD]Same Model Number?
[/TD]
[TD]Full Model Number 2016
[/TD]
[TD]Retailer
[/TD]
[TD]2015 MAP/MSRP
[/TD]
[TD]MTD Planning Selling Price
[/TD]
[TD]Brand
[/TD]
[TD]Planning ID
[/TD]
[TD]Main Model Name
[/TD]
[TD]Short Description
[/TD]
[TD]Supply drop dead date for prebuild of 9/1?
[/TD]
[TD]Comments
[/TD]
[TD]Okay to release item?
[/TD]
[TD]PMM Sheet Released
[/TD]
[TD]Configuration
[/TD]
[TD]Planning ID created/maintained
[/TD]
[TD]DFUtoSKU created with effectivity updated
[/TD]
[TD]Forecast Updated
[/TD]
[TD]Safety Stock updated
[/TD]
[TD]Last Year Annual
[/TD]
[TD]2016 Planning Volume
[/TD]
[TD]Supply Plan loaded
[/TD]
[TD]Supply Planning Volume
[/TD]
[TD="align: right"]12-Jun
[/TD]
[TD="align: right"]19-Jun
[/TD]
[TD="align: right"]26-Jun
[/TD]
[TD="align: right"]3-Jul
[/TD]
[TD="align: right"]10-Jul
[/TD]
[TD="align: right"]17-Jul
[/TD]
[TD="align: right"]24-Jul
[/TD]
[TD="align: right"]31-Jul
[/TD]
[TD="align: right"]7-Aug
[/TD]
[TD="align: right"]14-Aug
[/TD]
[TD="align: right"]21-Aug
[/TD]
[TD="align: right"]28-Aug
[/TD]
[TD="align: right"]4-Sep
[/TD]
[TD="align: right"]11-Sep
[/TD]
[TD="align: right"]18-Sep
[/TD]
[TD="align: right"]25-Sep
[/TD]
[TD="align: right"]2-Oct
[/TD]
[TD="align: right"]9-Oct
[/TD]
[TD="align: right"]16-Oct
[/TD]
[TD="align: right"]23-Oct
[/TD]
[TD="align: right"]30-Oct
[/TD]
[TD="align: right"]6-Nov
[/TD]
[TD="align: right"]13-Nov
[/TD]
[TD="align: right"]20-Nov
[/TD]
[TD="align: right"]27-Nov
[/TD]
[TD="align: right"]4-Dec
[/TD]
[TD="align: right"]11-Dec
[/TD]
[TD="align: right"]18-Dec
[/TD]
[TD="align: right"]25-Dec
[/TD]
[TD="align: right"]1-Jan
[/TD]
[TD="align: right"]8-Jan
[/TD]
[TD="align: right"]15-Jan
[/TD]
[TD="align: right"]22-Jan
[/TD]
[TD="align: right"]29-Jan
[/TD]
[TD="align: right"]5-Feb
[/TD]
[TD="align: right"]12-Feb
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]Yes
[/TD]
[TD]Yes
[/TD]
[TD]12345
[/TD]
[TD]IR
[/TD]
[TD]$399.99
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CC31AS2M5E
[/TD]
[TD]1X 21"
[/TD]
[TD][/TD]
[TD]#REF!
[/TD]
[TD]Same spec as 2015.
[/TD]
[TD]Yes*
[/TD]
[TD]No*
[/TD]
[TD]No*
[/TD]
[TD]No*
[/TD]
[TD]No*
[/TD]
[TD]No*
[/TD]
[TD]No*
[/TD]
[TD]June Forecast
[/TD]
[TD]0
[/TD]
[TD]No*
[/TD]
[TD][/TD]
[TD]x
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jeffrey Mahoney]

I need to go eat. Back to help more in a little while. Jeff

 

[Felix_Dragonhammer]

I'm sorry, I meant when the "Go Forward?" is set to "No".

 

[Jeffrey Mahoney]

I didn't realize you were providing RGB values for the color, Ha. Ok changed that. Did you try the code? Any way, this code has the correct color. Let me know if this works.

Private Sub Worksheet_Change(ByVal Target As Range)  Dim i As Range
  Dim Sht As Worksheet
  Dim R As Range
  Dim cel As Range


  Set i = Intersect(Target, Range("B2:B10000"))
  If Not i Is Nothing Then
    Set Sht = ActiveSheet
    If i.Text = "No" Then
      Set R = Range(Sht.Cells(i.Row, 1), Sht.Cells(i.Row, 200).End(xlToLeft))
      With R.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 3421846
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      For Each cel In R
        If cel.Text = "Yes" Then
          cel = "No"
        End If
      Next cel
    End If
  End If
        
      
      
End Sub

 

[Felix_Dragonhammer]

It didn't work.
Just to make sure I'm trying it out right, I went Developer>Visual Basic>Insert Module and copied in the code.

 

[Jeffrey Mahoney]

You need to find the "Sheet" module and paste it there. What is the name of your sheet or tab?

 

[Felix_Dragonhammer]

Wpd

 

[Felix_Dragonhammer]

This time I double clicked on the sheet name and pasted the code in the form that came up. No dice. Do I need to edit the sheet names in the code?

 

[Felix_Dragonhammer]

I am off to class. I'll be back on in approximately 5 hours.

 

[Felix_Dragonhammer]

An update: when I try to close the workbook, the following message appears: Compile Error: Expected End of Statement.

 

[Jeffrey Mahoney]

Not only did it work for me. I didn't get the error you mentioned