repeat Worksheet_Change event on various colums

[robertvdb]

I have a worksheet with some 200 columns. These columns make 20 blocks of 10 columns, each block has the similar structure. The same Worksheet-Change event needs to be triggered on, say, column A, column K, col T, and so on.

I can do this as indicated here below, but I am looking for a more condensed way.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim kol_A_rng As Range
Dim kol_K_rng As Range
Dim kol_T_rng As Range

Set kol_A_rng = Range("A3:A300")
Set kol_K_rng = Range("K3:K300")
Set kol_T_rng = Range("T3:T300")

If Target.Count = 1 And Not Application.Intersect(Target, kol_A_rng) Is Nothing Then
    'do something  (this something is approx. 30 lines !)
ElseIf Target.Count = 1 And Not Application.Intersect(Target, kol_K_rng) Is Nothing Then
    'do the same thing as in col A
ElseIf Target.Count = 1 And Not Application.Intersect(Target, kol_T_rng) Is Nothing Then
    'do the same thing as in col A
'and so on

Else
End if

End Sub

 

[dmt32]

Hi,
try following & see if does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng         As Range
    
    If Target.CountLarge > 1 Then Exit Sub
    
    ' range Areas
    Set rng = Me.Range("A3:A300,K3:K300,T3:T300")
    
    If Not Intersect(Target, rng) Is Nothing Then
        
        'do the same thing for all Areas
        
    End If
    
End Sub

Dave

 

[bebo021999]

If you are working with each block of 10 columns, start from column A (col 1), column K (col 11),, column U (col 21):

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   if .row >=3 and .row <=300 and (.column mod 10)=0 then
     ---main code here-----
   end if
end with
End sub

 

[robertvdb]

Thanks. I'll try both proposed solutions and I'll get back to you.

 

[robertvdb]

Thanks. I am happy with both suggested solutions.