anish.ms
11-15-2021, 12:37 PM
Hi Experts,
Request your help on the below -
Is there any way to reduce the length of the following part of code
If (rCell.Column = 6 Or rCell.Column = 7 Or rCell.Column = 8 Or rCell.Column = 9 Or rCell.Column = 10 Or rCell.Column = 11)
As the column numbers are adjust, is is possible to mention 6 to 11 or within an array?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim iMax As Long
iMax = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For Each rCell In Target.Cells
If (rCell.Column = 6 Or rCell.Column = 7 Or rCell.Column = 8 Or rCell.Column = 9 Or rCell.Column = 10 Or rCell.Column = 11) And (rCell.Row >= 3 And rCell.Row <= iMax) Then
Call InherentRisk(rCell)
ElseIf (rCell.Column = 12 Or rCell.Column = 13 Or rCell.Column = 14 Or rCell.Column = 15 Or rCell.Column = 16) And (rCell.Row >= 3 And rCell.Row <= iMax) Then
Call ResidualRisk(rCell)
End If
Next
End Sub
Thanks in Advance!
Request your help on the below -
Is there any way to reduce the length of the following part of code
If (rCell.Column = 6 Or rCell.Column = 7 Or rCell.Column = 8 Or rCell.Column = 9 Or rCell.Column = 10 Or rCell.Column = 11)
As the column numbers are adjust, is is possible to mention 6 to 11 or within an array?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim iMax As Long
iMax = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For Each rCell In Target.Cells
If (rCell.Column = 6 Or rCell.Column = 7 Or rCell.Column = 8 Or rCell.Column = 9 Or rCell.Column = 10 Or rCell.Column = 11) And (rCell.Row >= 3 And rCell.Row <= iMax) Then
Call InherentRisk(rCell)
ElseIf (rCell.Column = 12 Or rCell.Column = 13 Or rCell.Column = 14 Or rCell.Column = 15 Or rCell.Column = 16) And (rCell.Row >= 3 And rCell.Row <= iMax) Then
Call ResidualRisk(rCell)
End If
Next
End Sub
Thanks in Advance!