Help : Shorten code to check multiple columns in Worksheet_Change event

[anish.ms]

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!

[Paul_Hossler]

Try


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range, rChanged As Range
Dim iMax As Long




iMax = Cells(Rows.Count, 1).End(xlUp).Row

Set rChanged = Intersect(Target, Cells(3, 6).Resize(iMax - 2, 11))
If rChanged Is Nothing Then Exit Sub

For Each rCell In rChanged.Cells
Select Case rCell.Column
Case 6 To 11
Call InherentRisk(rCell)
Case 12 To 16
Call ResidualRisk(rCell)
End Select
Next
End Sub







As the column numbers are adjust, is is possible to mention 6 to 11 or within an array?

Don't understand. Example?

[anish.ms]

Thanks Paul!
I have attached the sample
If I have a value in cell A53, that should be the iMax but if choose a value in F53 it is not working

[Paul_Hossler]

1.


If I have a value in cell A53, that should be the iMax but if choose a value in F53 it is not working

Actually, I think it is working, Your data validations do not extend into row 53.

If I put a value into A53 and manually type the responses, it seems to work

Look at the attachment


2. Two suggestions to code below



Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range, rChanged As Range
Dim iMax As Long


iMax = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Set rChanged = Intersect(Target, Cells(3, 6).Resize(iMax - 2, 11))
If rChanged Is Nothing Then Exit Sub

Application.EnableEvents = False ' <<<<<<<<<<Moved after Exit Sub above
Application.ScreenUpdating = False

For Each rCell In rChanged.Cells
Select Case rCell.Column
Case 6 To 11
Call InherentRisk(rCell)
Case 12 To 16
Call ResidualRisk(rCell)
End Select
Next

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



.Cells(11).Interior.ColorIndex = xlColorIndexNone ' <<<<<<<<<<<<< Not Null

[p45cal]

If rCell.Column > 11 then Call ResidualRisk(rCell) Else Call InherentRisk(rCell)instead of Select Case?

[Paul_Hossler]

instead of Select Case?

Less typing is good :thumb

Either will work, but I tend to prefer Select Case since for me I find the code easier to read (probably since I'm more used to it that way)

[anish.ms]

Thanks Paul for your help and advice!
Thanks p45cal for your time !