Using Data Validation in conjunction with VBA

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
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>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,020
Office Version
  1. 365
Platform
  1. Windows
An update: when I try to close the workbook, the following message appears: Compile Error: Expected End of Statement.
Any time an error occurs, make sure to debug and record which line the error was on.

Did you make sure to remove the code from the standard module? Can you take a screen shot of the VBA windows where you pasted the code? I don't think there are any differences between 2010 and 2013 to make it fail.

Jeff
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
I was able to create a macro that accomplished all the formatting I want to do. However, it is triggered by hitting the keys Ctrl+Shift+N. However, I would prefer so that it is triggered when I change a validation in column B to "No". I'll be looking at your current code and browsing the internet to try and accomplish this while I await your reply, but I wouldn't mind a little help.

Here is my code.

Code:
Sub GoForwardNo()
'
' GoForwardNo Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.Range("A1:B1,O1:U1,X1").Select
    ActiveCell.Offset(0, 23).Range("A1").Activate
    Selection.Clear
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveWindow.SmallScroll ToRight:=-4
    ActiveCell.Offset(0, -23).Range("A1").Select
    ActiveCell.FormulaR1C1 = "No "
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, 13).Range("A1").Activate
    ActiveCell.Range("A1,B1,C1,D1,E1,F1,G1").Select
    ActiveCell.Offset(0, 6).Range("A1").Activate
    ActiveCell.Offset(0, -6).Range("A1,B1,C1,D1,E1,F1,G1,J1").Select
    ActiveCell.Offset(0, 3).Range("A1").Activate
    ActiveSheet.Paste
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Range("A1:BJ1").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
End Sub
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
The form I paste it in says this at the top: Excel Forum Question Example.xslm - Sheet1 (Code).

I wasn't able to paste in a picture.
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117

ADVERTISEMENT

Another point of interest is that when I tried to run the macro, it conflicted with your code and pulled up another error. (With one of the Dim phrases or something).
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
I found a bit of code similar to what I need.

Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("T:T")) Is Nothing Then 
ActiveSheet.Unprotect 
Dim Cell As Integer 

Range("T2").Select 
Cell = 2 

Do Until Selection = "" 

If Selection = "NO" Then 
Range("A" & Cell & ":T" & Cell).Locked = False 

ElseIf Selection = "YES" Then 
Range("A" & Cell & ":T" & Cell).Locked = True 

End If 

Cell = Cell + 1 

Selection.Offset(1, 0).Select 

Loop 

ActiveSheet.Protect contents:=True, userInterfaceOnly:=True 
ActiveSheet.EnableAutoFilter = True 

End If 

End Sub
Not sure how I would modify this to fit my needs, however.
UPDATE: Found another, more specific code that would help.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.EnableEvents = False
Call mymacro
End If
Application.EnableEvents = True
End Sub
 
Last edited:

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117

ADVERTISEMENT

I tried to edit my macro slightly, and now it's throwing a an error. (error in bold 9th row from bottom)
Code:
Sub GoForwardNo()
'
' GoForwardNo Macro
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.Range("A1:B1,O1:U1,X1").Select
    ActiveCell.Offset(0, 23).Range("A1").Activate
    Selection.Clear
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.Offset(0, -23).Range("A1").Select
    ActiveCell.FormulaR1C1 = "No "
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, 13).Range("A1").Activate
    ActiveCell.Range("A1,B1,C1,D1,E1,F1,G1").Select
    ActiveCell.Offset(0, 6).Range("A1").Activate
    ActiveCell.Offset(0, -6).Range("A1,B1,C1,D1,E1,F1,G1,J1").Select
    ActiveCell.Offset(0, 3).Range("A1").Activate
    ActiveSheet.Paste
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    [B]ActiveCell.Range("A1:BK").Select
[/B]   With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    ActiveCell.Offset(1, 1).Select
    End With
End Sub
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
Never mind, I fixed it, it was missing a 1 after the "BK". However, I would still appreciate any help getting it to run after I change a cell value in column B to "No".
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,020
Office Version
  1. 365
Platform
  1. Windows
Felix, I'm having a hard time helping you. For me to help, I need specific and detailed input because I'm not sitting in front of your computer.
Another point of interest is that when I tried to run the macro, it conflicted with your code and pulled up another error. (With one of the Dim phrases or something).

Just guessing, but I think the code you pasted that I gave you might have the first "Dim" line at the end of the "SUB" line.
Code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)  Dim i As Range[/COLOR]  Dim Sht As Worksheet [COLOR=#333333]  Dim R As Range
[/COLOR]...
Needs to be
Code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)  

Dim i As Range[/COLOR]  
Dim Sht As Worksheet [COLOR=#333333] 
Dim R As Range
[/COLOR]
 

Felix_Dragonhammer

Board Regular
Joined
Apr 7, 2015
Messages
117
Unfortunately, the change didn't have the desired effect. However, there seems to be some progress. Whenever I type any value in column B, the cell is filled with the desired color. None of the other cells in that row are affected.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top