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
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.