Solved: Private Sub Worksheet_Change(ByVal Target As range)

[CJHAMILTON]

Sigh, can anyone give me a clue? I have been trying to figure out how to LOOP through EACH cell of the Target range returned by the Worksheet_Change event.

I have a rather long sequence of tests that works great when a the Target is a single cell. The problem arises when the Target is a multi-cell range. I would like the code to runs through the sequence of tests FOR EACH cell in the Target.

An example of the code is below. It real code is considerable longer than the example, but I think the example is sufficient to understand the question I am asking.

What would I put after the For Each statement to loop through the code for EACH cell in the Target range? Any help here would be greatly appreciated. I simply cannot figure this out.

Private Sub Worksheet_Change(ByVal Target As range)
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
For Each ? (WHAT DO I PUT HERE?)
'DSR
If Target.Column = 6 And Target.Row >= 8 Then
blnBuildDescription = True
End If
'DSR Index Additional (G;7)
If Target.Column = 7 And Target.Row >= 8 Then
If Not funDSRValidation(Target.Value) Then
Target.Value = ""
GoTo LastLine
End If
Target.Value = _
UCase(Target.Value)
blnBuildDescription = True
End If
'Part to Connect - Vendor Submittal (I;9)
If Target.Column = 9 And Target.Row >= 8 Then
If Not funPartToConnectValidation(Target.Value) Then
Target.Value = ""
GoTo LastLine
End If
Target.Value = _
UCase(Target.Value)
Cells(Target.Row, Target.Column + 1).ClearContents
End If
'Part to Connect - Specification (J;10)
If Target.Column = 10 And Target.Row >= 8 Then
If Not funPartToConnectValidation(Target.Value) Then
Target.Value = ""
GoTo LastLine
End If
Target.Value = _
UCase(Target.Value)
Cells(Target.Row, Target.Column - 1).ClearContents
End If
'COA (L;12)
If Target.Column = 12 And Target.Row >= 8 Then
If Not funCOAValidation(Target.Value) Then
Target.Value = ""
GoTo LastLine
End If
Target.Value = _
UCase(Target.Value)
End If
'Created On date (M;13)
If Target.Column = 13 And Target.Row >= 8 Then
If Not funDateValidation(Target.Value) Then
Target.Value = ""
GoTo LastLine
End If
End If

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

For Each cell In Target

If cell.Row >= 8 Then

Select Case cell.Column

'DSR
Case 6:
blnBuildDescription = True

'DSR Index Additional (G;7)
Case 7:
If Not funDSRValidation(cell.Value) Then

cell.Value = ""
GoTo LastLine
End If
cell.Value = UCase(cell.Value)
blnBuildDescription = True

'Part to Connect - Vendor Submittal (I;9)
Case 9:
If Not funPartToConnectValidation(cell.Value) Then

cell.Value = ""
GoTo LastLine
End If
cell.Value = UCase(cell.Value)
Cells(cell.Row, cell.Column + 1).ClearContents

'Part to Connect - Specification (J;10)
Case 10:
If Not funPartToConnectValidation(cell.Value) Then

cell.Value = ""
GoTo LastLine
End If
cell.Value = UCase(cell.Value)
Cells(cell.Row, cell.Column - 1).ClearContents

'COA (L;12)
Case 12:
If Not funCOAValidation(cell.Value) Then

cell.Value = ""
GoTo LastLine
End If
cell.Value = UCase(cell.Value)

'Created On date (M;13)
Case 13:
If Not funDateValidation(cell.Value) Then

cell.Value = ""
GoTo LastLine
End If
End If
End Select
Next cell

[mdmackillop]

Welcome to VBAX

Best to check functionality with something very simple and easily checked, then add in your more complex code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel as range
For Each cel In Target
MsgBox cel.Address
Next
End Sub

[CJHAMILTON]

Thank you both for your prompt response. xld, you had an excellent suggestion and layout; work like a charm.