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

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 !