If you open the VB Editor, you will see something like this down the left of the screen:
- VBAProject (xxxxxx.xlsm)
- Microsoft Excel Objects
- Sheet1 (Sheet1)
- Sheet2 (Sheet2)
- Sheet3 (Sheet3)
- ThisWorkbook
- Modules
Module1
Module2
On the screen, the minuses will be in little boxes. If any of the minuses is a plus, click it to expand that part of the project’s contents.
If you have renamed your sheets, the first “Sheet1”, “Sheet2” and so on will be unchanged since there are Excel’s permanent names for the sheets but the second copies will have been replaced by your names. If you have renamed your modules, “Module1” and so on will have been replaced by your names.
Click “ThisWorkbook” and the code area to the right will clear. You can place any code you wish in this code area but it is primarily intended for workbook level event routines.
An event routine is VBA code that Excel will execute when the associated event occurs. So Sub Workbook_Open will be executed when the workbook is opened.
Now click “SheetN (SheetN)” where SheetN is the worksheet containing the cells you wish to monitor. Again you will get an empty code area. This code area is associated with SheetN. You use it for event routines that are only associated with SheetN.
Copy the code below into the code area.
I have written a Worksheet_Change routine for you. Every time a cell or a range or a union of ranges within SheetN changes, this routine is executed with a parameter which is a Range listing all the cells and/or ranges that have changed. This routine is not executed for changes in other worksheets.
The heart of this routine are the following three arrays:
CellsToMonitor = Array("B6", "B9", "B12", "B15")
ExpectedValue = Array("Tractors", "Tractors", "Tractors", "Tractors")
CellsToClear = Array("H7", "H10", "H13", "H16")
CellsToMonitor lists the cells to monitor. Your question lists B6 and B9. I have added a couple more cells for testing purposes. You can add or subtract from this list as required.
Your question implies that in every case, the expected value is “Tractors”. I have placed the expected values in an array so you can have different values if you wish.
CellsToClear lists the cells to be cleared.
All this is explained in more detail within the event routine.
I have attempted to test the routine for every type of change that might occur. You will need to play with the routine to fully understand how it works so you will probably end up repeating my testing. Come back with questions if necessary but, the more you can work out for yourself, the faster you will develop.
Private Sub Worksheet_Change(ByVal Target As Range)
' Target can be:
' * a single cell such as A1
' * a range such as A1:B2
' * a union of cells and ranges such as A1:B2, C3, D5
' Every cell in the range Target has been changed by the user or a macro.
' Stop the clearing of a cell from calling this routine
Application.EnableEvents = False
Dim CellsToMonitor As Variant
Dim ExpectedValue As Variant
Dim CellsToClear As Variant
CellsToMonitor = Array("B6", "B9", "B12", "B15")
ExpectedValue = Array("Tractors", "Tractors", "Tractors", "Tractors")
CellsToClear = Array("H7", "H10", "H13", "H16")
' * CellsToMonitor is a list of cells within the worksheet that are to be monitored.
' The value of cells not in this list are not of interest. The code assumes the
' column codes in this array are uppercase; so "B6" not "b6".
' * ExpectedValue is a list of the expected values for the cells to monitor. In the
' question all these values are the same. This array allows the expected values
' to be different.
' * CellsToClear identifies the cell to be cleared if a monitored cell does not
' have the expected values.
' * The three arrays must have the same number of elements. If CellsToMonitor(N)
' is one of the cells in Target and if Range(CellsToMonitor(N)).Value is not
' equal to ExpectedValue(N) then Range(CellsToClear(N)) is cleared
Dim ColCrnt As Long
Dim InxMon As Long
Dim InxTA As Long
Dim RngCrnt As Range
Dim RowCrnt As Long
Dim TgtAddrPart() As String
Dim TgtCellAddr As String
Dim WshtTgt As Worksheet
' The cells within Target could have been changed by a macro so ActiveSheet
' does not have to be the worksheet being monitored.
Set WshtTgt = Target.Worksheet
TgtAddrPart = Split(Target.Address, ",")
For InxTA = LBound(TgtAddrPart) To UBound(TgtAddrPart)
Set RngCrnt = WshtTgt.Range(TgtAddrPart(InxTarget))
' RngCrnt.Row is the first row in the range
' RngCrnt.Rows.Count is the number of rows in the range.
' ColCrnt.Column and RngCrnt.Columns.Count are the same but for columns
For RowCrnt = RngCrnt.Row To RngCrnt.Row + RngCrnt.Rows.Count - 1
For ColCrnt = RngCrnt.Column To RngCrnt.Column + RngCrnt.Columns.Count - 1
' Debug.Print "Target cell = Cells(" & RowCrnt & ", " & ColCrnt & ")"
' Create A1 format address for current target cell
TgtCellAddr = ColNumToCode(ColCrnt) & RowCrnt
For InxMon = LBound(CellsToMonitor) To UBound(CellsToMonitor)
If TgtCellAddr = CellsToMonitor(InxMon) Then
' Have match on address
If WshtTgt.Cells(RowCrnt, ColCrnt).Value <> ExpectedValue(InxMon) Then
' Do not have match on expected value so clear linked cell
WshtTgt.Range(CellsToClear(InxMon)).ClearContents
End If
Exit For
End If
Next ' For each cell to monitor
Next ' For each column within Range within Target
Next ' For each row within Range within Target
Next ' For each Range within Target
Application.EnableEvents = True
End Sub
Function ColNumToCode(ByVal ColNum As Long) As String
Dim ColCode As String
Dim PartNum As Long
' Last updated 3 Feb 12. Adapted to handle three character codes.
If ColNum = 0 Then
ColNumToCode = "0"
Else
ColCode = ""
Do While ColNum > 0
PartNum = (ColNum - 1) Mod 26
ColCode = Chr(65 + PartNum) & ColCode
ColNum = (ColNum - PartNum - 1) \ 26
Loop
End If
ColNumToCode = ColCode
End Function
