Looping code while changing row reference incrementally

I have a spreadsheet where I want 2 adjacent cells to be exclusive, meaning you can only enter a value in one of the 2 cells. I got the code to work properly when reference a specific cell range.

However i have this logic repeating every 4 rows within the spreadsheet. I tried to create an integer value to incrementally increase the row reference but the action with message box are not being applied to the "next" integer.

Any help would be appreciate. below is what i tried with declaring J as my row reference via integer data type. Right now i have j set to 34-146. If i put an "x" in D34 and then put an "X" in E34 i get the message and the value entered in E34 is cleared- which is what i want. When i do the same in D38 and E38, i do not get any message and the data entry remains in both cells.

Private Sub Worksheet_Change(ByVal Target As Range)

  Dim j As Integer
  For j = 34 To 146 Step 4

  Dim rLook As Range
  Set rLook = Range("D" & j & ":E" & j)
  Dim wf As WorksheetFunction
  Set wf = Application.WorksheetFunction

  If Intersect(Target, rLook) Is Nothing Then Exit Sub

  If wf.CountA(rLook) < 2 Then Exit Sub

  Application.EnableEvents = False
    Target.ClearContents
    MsgBox ("Only one entry allowed. Please select either Blanket or User Specific.")

  Application.EnableEvents = True

  Next j

End Sub

Answers


If I understand correctly what you are after, there isn't any need to loop through cells - Target will always be the cell that changed. All you should need is a simple test to see if the adjacent cell matches:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim matching As Boolean

    If Target.Column = 4 Then
        matching = (Target.Value = Target.Offset(0, 1).Value)
    ElseIf Target.Column = 5 Then
        matching = (Target.Value = Target.Offset(0, -1).Value)
    End If

    If matching Then
         Application.EnableEvents = False
         Target.ClearContents
         Application.EnableEvents = True
         MsgBox "Only one entry allowed. Please select either Blanket or User Specific."
    End If

End Sub

If you need to limit it to specific rows, just wrap the whole thing in another If that tests to see if Target.Row is one you care about.


Need Your Help

Is there an easy way to generate a graph of Ansible role dependencies?

scalability administration ansible orchestration

Since version 1.3, Ansible has supported role dependencies to encourage reuse of role definitions. To audit and maintain larger orchestrations, it would be nice to have some way to easily generate a