LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 115
Default One solution solved one more to go. Loop & Cell variables?

Something's not quite right...

What Im looking for is that when Q12 is selected, the R11 is set to TRUE,
when it is selected again, it is set to FALSE. This worked:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

Cancel = True
End Sub

On the Worksheet, I may copy Rows (11-22), sometimes a few times, sometimes
a lot. I want the same clicking on its €śQ€ť cell (Q12, Q24, Q36...) depending
on how many times I copy the rows to toggle it's "R" cell (R11, R23, R35) .
The loop can end when the €śA€ť row, (A13, A25, A37) is tested NOT to have a
value.
Rows are in multiples of 12€¦
Target.Address = "$Q$12" and Range("R11") (BTW€¦ A13 is a number)
Target.Address = "$Q$24" and Range("R23") (A25 is a number)
Target.Address = "$Q$36" and Range("R35") (A37 is a number)
Etc€¦

I tried the code in the last reply but it does not set the value of R to
TRUE/FALSE with the €śQ€ť cell is selected.

I was just hopping to turn this into a loop so that the number of rows I
copy can be more dynamic€¦ Manually entering the code below works:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$Q$12" Then
If UCase(Range("R11")) = "TRUE" Then
Range("R11") = "FALSE"
Else
Range("R11") = "TRUE"
End If
End If

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

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

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

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

Cancel = True
End Sub

,,,etc,,,


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting Multiple Variables with a loop jlclyde Excel Discussion (Misc queries) 6 November 11th 09 09:40 PM
solved problem claude Excel Worksheet Functions 1 July 7th 06 08:14 PM
Excel VBA - changing variables during a loop ellis_x[_3_] Excel Programming 3 July 5th 04 08:44 AM
Newbie. Please help me find a loop solution Nigel Brown[_3_] Excel Programming 1 July 16th 03 02:48 PM
Newbie. Please help me find a loop solution Nigel Brown[_2_] Excel Programming 0 July 16th 03 10:25 AM


All times are GMT +1. The time now is 11:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"