Function arguments

[timlh42]

I have the following


If Not Intersect(Target, Range("H7:H37, I7:I37, N7:N37, O7:O37")) Is Nothing Then
If Target.Count > 1 Then Exit Sub

As in the above example, the range of cells will continue to be 2 columns and then skip 4 columns, 2 columns and then skip 4 columns, etc. This pattern will continue for 180 times.

Is there any way to do this without physically typing in every range in the argument?

 

[pgc01]

Hi

If I unserstand correctly you have 180 groups of 6 columns for which you are intersted in the first 2 columns of each group.

The columns are 8:1087(13+179*6)
The rows you want are 7:37


One options is:

If Target.Row < 7 Or Target.Row > 37 Then Exit Sub
If Target.Column < 8 Or Target.Column > 1087 Then Exit Sub
If (Target.Column - 8) Mod 6 > 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub

or, you can combine them:

If (Target.Row < 7 Or Target.Row > 37) Or (Target.Column < 8 Or Target.Column > 1087) Or _
   ((Target.Column - 8) Mod 6 > 1) Or (Target.Count > 1) Then Exit Sub

 

[timlh42]

That works great!!!! Thank you so much!!!!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[timlh42]

One more quick question. Is there a way to add another argument to the above?

I would like to have column H and every 6th column afterward call the userform if the value is greater than 2

 

[pgc01]

Hi

Not sure I understand correctly, check this that covers both cases

If Target.Row < 7 Or Target.Row > 37 Then Exit Sub
If Target.Column < 8 Or Target.Column > 1087 Then Exit Sub
If Target.Count > 1 Then Exit Sub


If (Target.Column - 8) Mod 6 <= 1 Then

    ' code for first case, first 2 columns out of each group of 6

End If


If (Target.Column - 8) Mod 6 = 0 Then

       ' code for second case, first column out of each group of 6

End If

 

[timlh42]

Brilliant!!!!

Thank you so much!!!!!