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 can help you with that Felix. I'm not completely understanding the color changes with the numbers?? Do you want certain colors based on a cell value?

 

[Jeffrey Mahoney]

Will the "Go Forward" column be in the same column (as in column B)?

 

[Jeffrey Mahoney]

Some of the cells have No*. Did you want those cells to change to just No? Here is some code, it needs work based on your responses and input.

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 = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      For Each cel In R
        If cel.Text = "*" Then
          cel = "No"
        End If
      Next cel
    End If
  End If
        
      
      
End Sub

This only uses one color. It's a start. Did you want the color red to disappear if the user changes it back to Yes?

 

[Jeffrey Mahoney]

I forgot to mention: Do you know about pasting this code in the Sheet module of VBA? In the VBA module, look for the sheet name to the left; double click on that; paste the code above into the code area to the right.

 

[Felix_Dragonhammer]

Will the "Go Forward" column be in the same column (as in column B)?

Yes.

 

[Felix_Dragonhammer]

Some of the cells have No*. Did you want those cells to change to just No?QUOTE]
The Nos with the asterisks are Data Validation Lists currently set to "No". When "Go Forward?" is set to "Yes" I want the Data Validation Lists to be replaced with the value "No".

 

[Felix_Dragonhammer]

Some of the cells have No*. Did you want those cells to change to just No? Here is some code, it needs work based on your responses and input.

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 = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      For Each cel In R
        If cel.Text = "*" Then
          cel = "No"
        End If
      Next cel
    End If
  End If
        
      
      
End Sub

This only uses one color. It's a start. Did you want the color red to disappear if the user changes it back to Yes?

Yes, I'd like the original fill I have to reappear if it's switched back to "Yes". I forgot to mention, I would also like the Data Validation Lists to reappear in their respective columns if it is switched back. Don't know if that has an impact.

 

[Felix_Dragonhammer]

And thanks for your help with this thus far.

 

[Jeffrey Mahoney]

I'm trying to understand better. Based on this statement:

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).

You wanted every validation cell in the row that has an asterisk to be changed to "No" if the "Go Forward" column changes to No.


Based on this statement:

The Nos with the asterisks are Data Validation Lists currently set to "No". When "Go Forward?" is set to "Yes" I want the Data Validation Lists to be replaced with the value "No".

When the Go Forward cell changes to "Yes", you want the other validation cells to change to "No"?

Please confirm which one you want.

And then let's deal with the colors after that.

Thank you