Tring to look this dynamicaly instead of manual code.?

[Guest]

I have the following code on a module:
This Worksheet is created dynamically and can have a different number of
rows...
I am trying to create a loop that can make this more dynamic so that instead
of assuming this needs to be replicated 20 times, it could run through a loop
until a cell value is = "" I can't quite get it...
Here is the manual code... below that will be my attempt.

The first loop could test a value from a different worksheet:
src = srcProgramDataInputWs.Cells(i - 8, 2).Value (B3; then B15; B25,
etc...) and wthen test = ""
------------------
If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Range("A16").Select
Else
Range("R11") = "TRUE"
Range("A16").Select
End If
End If

If Target.Address = "$Q$24" Then
If UCase(Range("R23")) = "TRUE" Then
Range("R23") = "FALSE"
Range("A28").Select
Else
Range("R23") = "TRUE"
Range("A28").Select
End If
End If

If Target.Address = "$Q$36" Then
If UCase(Range("R35")) = "TRUE" Then
Range("R35") = "FALSE"
Range("A40").Select
Else
Range("R35") = "TRUE"
Range("A40").Select
End If
End If

If Target.Address = "$Q$48" Then
If UCase(Range("R47")) = "TRUE" Then
Range("R47") = "FALSE"
Range("A52").Select
Else
Range("R47") = "TRUE"
Range("A52").Select
End If
End If

If Target.Address = "$Q$60" Then
If UCase(Range("R59")) = "TRUE" Then
Range("R59") = "FALSE"
Range("A64").Select
Else
Range("R59") = "TRUE"
Range("A64").Select
End If
End If

If Target.Address = "$Q$72" Then
If UCase(Range("R71")) = "TRUE" Then
Range("R71") = "FALSE"
Range("A76").Select
Else
Range("R71") = "TRUE"
Range("A76").Select
End If
End If

If Target.Address = "$Q$84" Then
If UCase(Range("R83")) = "TRUE" Then
Range("R83") = "FALSE"
Range("A88").Select
Else
Range("R83") = "TRUE"
Range("A88").Select
End If
End If

If Target.Address = "$Q$96" Then
If UCase(Range("R95")) = "TRUE" Then
Range("R95") = "FALSE"
Range("A100").Select
Else
Range("R95") = "TRUE"
Range("A100").Select
End If
End If

If Target.Address = "$Q$108" Then
If UCase(Range("R107")) = "TRUE" Then
Range("R107") = "FALSE"
Range("A112").Select
Else
Range("R107") = "TRUE"
Range("A112").Select
End If
End If

If Target.Address = "$Q$120" Then
If UCase(Range("R119")) = "TRUE" Then
Range("R119") = "FALSE"
Range("A124").Select
Else
Range("R119") = "TRUE"
Range("A124").Select
End If
End If

If Target.Address = "$Q$132" Then
If UCase(Range("R131")) = "TRUE" Then
Range("R131") = "FALSE"
Range("A136").Select
Else
Range("R131") = "TRUE"
Range("A136").Select
End If
End If

If Target.Address = "$Q$144" Then
If UCase(Range("R143")) = "TRUE" Then
Range("R143") = "FALSE"
Range("A148").Select
Else
Range("R143") = "TRUE"
Range("A148").Select
End If
End If

If Target.Address = "$Q$156" Then
If UCase(Range("R155")) = "TRUE" Then
Range("R155") = "FALSE"
Range("A160").Select
Else
Range("R155") = "TRUE"
Range("A160").Select
End If
End If

If Target.Address = "$Q$168" Then
If UCase(Range("R167")) = "TRUE" Then
Range("R167") = "FALSE"
Range("A172").Select
Else
Range("R167") = "TRUE"
Range("A172").Select
End If
End If

If Target.Address = "$Q$180" Then
If UCase(Range("R179")) = "TRUE" Then
Range("R179") = "FALSE"
Range("A184").Select
Else
Range("R179") = "TRUE"
Range("A184").Select
End If
End If

If Target.Address = "$Q$192" Then
If UCase(Range("R191")) = "TRUE" Then
Range("R191") = "FALSE"
Range("A196").Select
Else
Range("R191") = "TRUE"
Range("A196").Select
End If
End If

If Target.Address = "$Q$204" Then
If UCase(Range("R203")) = "TRUE" Then
Range("R203") = "FALSE"
Range("A208").Select
Else
Range("R203") = "TRUE"
Range("A208").Select
End If
End If

If Target.Address = "$Q$216" Then
If UCase(Range("R215")) = "TRUE" Then
Range("R215") = "FALSE"
Range("A220").Select
Else
Range("R215") = "TRUE"
Range("A220").Select
End If
End If

If Target.Address = "$Q$228" Then
If UCase(Range("R227")) = "TRUE" Then
Range("R227") = "FALSE"
Range("A232").Select
Else
Range("R227") = "TRUE"
Range("A232").Select
End If
End If

If Target.Address = "$Q$240" Then
If UCase(Range("R239")) = "TRUE" Then
Range("R239") = "FALSE"
Range("A244").Select
Else
Range("R239") = "TRUE"
Range("A244").Select
End If
End If
------------------

------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim srcProgramDataInputWs As Worksheet
Dim i As Integer
Dim src As Variant

Set srcProgramDataInputWs = Sheets("ProgramDataInput")
src = srcProgramDataInputWs.Range("B3").Value
i = 11
Do Until src = ""
If Target.Address = "$Q$" & i + 1 Then
If UCase(Range("R" & i)) = "TRUE" Then
Range("R" & i) = "FALSE"
Range("A" & i + 5).Select
Else
Range("R" & i) = "TRUE"
Range("A" & i + 5).Select
End If
End If
i = i + 12
src = srcProgramDataInputWs.Cells(i - 8, 2).Value
Loop

Cancel = True
End Sub
------------------

 

[Guest]

This is according to my interpretation:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim c As Range
Dim i As Long

Set ws = Sheets("ProgramDataInput")
i = 0
Application.EnableEvents = False
With Target
If .Column <> 17 Or .Count > 1 Then GoTo ExitProc
Do
i = i + 12
If .Row = i Then
.Offset(-1, 1) = Not .Offset(-1, 1)
.Offset(4, -16).Select
Exit Do
End If
Set c = ws.Cells(i - 9, 2)
Loop Until c.Value = ""
End With
ExitProc:
Application.EnableEvents = True
End Sub

Regards,
Greg

 

[Guest]

A slight change to my post. Minimal testing and based on theory only:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim c As Range
Dim i As Long

Set ws = Sheets("ProgramDataInput")
i = 0
Set c = ws.Range("B3")
With Target
If .Column <> 17 Or .Count > 1 Then Exit Sub
Application.EnableEvents = False
Do Until c.Value = ""
i = i + 12
If .Row = i Then
.Offset(-1, 1) = Not .Offset(-1, 1)
.Offset(4, -16).Select
Else
Set c = ws.Cells(i + 3, 2)
End If
Loop
End With
Application.EnableEvents = True
End Sub

Regards,
Greg