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