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.

Full Model Number 2015

Go Forward?

Same Model Number?

Full Model Number 2016

Retailer

2015 MAP/MSRP

MTD Planning Selling Price

Brand

Planning ID

Main Model Name

Short Description

Supply drop dead date for prebuild of 9/1?

Comments

Okay to release item?

PMM Sheet Released

Configuration

Planning ID created/maintained

DFUtoSKU created with effectivity updated

Forecast Updated

Safety Stock updated

Last Year Annual

2016 Planning Volume

Supply Plan loaded

Supply Planning Volume

12-Jun

19-Jun

26-Jun

3-Jul

10-Jul

17-Jul

24-Jul

31-Jul

7-Aug

14-Aug

21-Aug

28-Aug

4-Sep

11-Sep

18-Sep

25-Sep

2-Oct

9-Oct

16-Oct

23-Oct

30-Oct

6-Nov

13-Nov

20-Nov

27-Nov

4-Dec

11-Dec

18-Dec

25-Dec

1-Jan

8-Jan

15-Jan

22-Jan

29-Jan

5-Feb

12-Feb

12345

Yes

Yes

12345

IR

$399.99

CC31AS2M5E

1X 21"

#REF!

Same spec as 2015.

Yes*

No*

No*

No*

No*

No*

No*

June Forecast

0

No*

x

<tbody>
</tbody>

 

[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