help selecting a cell
Posted by steve on February 23, 2001 9:25 PM
: Change Range("A1:S10") to suit. Push Alt+Q.
THATS CORRECT!
I WANT TO HAVE 28 ROWS A4 TO S4 AND T4 TO CONTAIN THE SELECTION( LETS SAY IF ANYTHING IN THE RANGE OF A4 TO S4 ONLY BE DISPLAYED IN T4 AND SO ON FOR EACH ROW) AND TO REPEAT FOR ROW 5-32 AND TO HAVE T5-T32 CONTAIN THE SELECTIONS FOR EACH ROW. I WANT ALL THE ROWS TO BE INDEPENDENT I WOULD ALSO LIKE TO FIND A WAY OF CIRCLEING THE CELL THAT WAS SELECTEDAND TO HAVE IT CHANGE IF YOU PICK A DIFFERENT CELL IN THE SAME ROW.
THANKS STEVE
Posted by Dave Hawley on February 24, 2001 1:56 AM
Steve, try this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("A1:S10")
If Not Intersect(Target, WatchRange) Is Nothing Then
Rw = Target.Row
Range("T" & Rw) = Target
End If
Set WatchRange = Nothing
End Sub
Change "WatchRange" to suit.
Dave
OzGrid Business Applications
Posted by STEVE on February 24, 2001 8:33 AM
I TRYED CHANGEING THE WATCHRANGE AND ALSO TRIED TO CHANGE IT TO WORK WITH ONE CELL BY USING TARGET ADDRESS BUT I CAN'T SEEM TO GET IT TO WORK.
STEVE
Posted by STEVE on February 24, 2001 8:37 AM
I PASTED IT AGAIN AND IT WORKS( KNOW HOW TO CIRCLE THE ONE I SELECTED )
Posted by Dave Hawley on February 24, 2001 9:02 AM
Re: I PASTED IT AGAIN AND IT WORKS( KNOW HOW TO CIRCLE THE ONE I SELECTED )
Post your code here and I'll take a look.
Dave
OzGrid Business Applications
Posted by STEVE on February 24, 2001 9:37 AM
Re: ( KNOW HOW TO CIRCLE THE ONE I SELECTED )
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:T10")
If Not Intersect(Target, WatchRange) Is Nothing Then
Rw = Target.Row
Range("U" & Rw) = Target
End If
Set WatchRange = Nothing
End Sub
Posted by Dave Hawley on February 24, 2001 9:58 AM
Re: ( KNOW HOW TO CIRCLE THE ONE I SELECTED )
Ok, I typed the number 5 in cell E5, then re-selected the cell and the number appeared in cell U5. Isn't that what you wanted ?
Dave
OzGrid Business Applications
Posted by STEVE on February 24, 2001 10:05 AM
YES , YOU MISS UNDERSTOOD
DAVE,
I HAVE THAT PART WORKING FINE THANKS, WHAT I'M TRYING KNOW IS TO CIRCLE THE ONE THAT I SELECTED. SORRY FOR THE CONFUSION.
THANKS AGAIN STEVE
Posted by Dave Hawley on February 24, 2001 10:18 AM
Re: YES , YOU MISS UNDERSTOOD
Steve, not sure about circling the selected cell, but you can color the cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:T10")
If Not Intersect(Target, WatchRange) Is Nothing Then
With Target
Rw = .Row
Range("E" & Rw & ":" & "T" & Rw). _
Interior.ColorIndex = xlColorIndexNone
.Interior.ColorIndex = 6
End With
Range("U" & Rw) = Target
End If
Set WatchRange = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by steve on February 24, 2001 10:39 AM
how about using autoshaoes
dave, I already use conditional formating in some cells, I think I'll go about doing this by trying the autoshape circle, but I think I will have to place one in each cell and have it activated when that cell is selected.
Posted by steve on February 24, 2001 10:49 AM
another idea(can we modify this code to box in the cell with a border)
davehow about instead of circleing to box in the cell with a red bold border in the format cell.
would that work.
thanks steve
Posted by Dave Hawley on February 24, 2001 11:08 AM
Re: another idea(can we modify this code to box in the cell with a border)
Steve, how about this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:T10")
If Not Intersect(Target, WatchRange) Is Nothing Then
With Target
Rw = .Row
Range("E" & Rw & ":" & "T" & Rw).Borders.LineStyle = xlNone
.BorderAround LineStyle:=xlContinuous, _
Weight:=xlMedium, ColorIndex:=3
End With
Range("U" & Rw) = Target
End If
Set WatchRange = Nothing
End Sub
OzGrid Business Applications
Posted by steve on February 24, 2001 11:22 AM
Re: another idea(can we modify this code to box in the cell with a border)
dave
works good but it makes all the normal borders disappear in each range when I select a cell in the range. I have it working with the font color also. I need normal borders there so I might just stick with font color, but I'd like to do both.
Thanks for all the help
Posted by Celia on March 04, 2001 7:01 AM
This macro will circle the selected cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
Dim h As Single, w As Single
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:T10")
If Not Intersect(Target, WatchRange) Is Nothing Then
ActiveSheet.Ovals.Cut
With Target
Rw = .Row
h = .Height * 0.2
w = .Width * 0.2
ActiveSheet.Ovals.Add _
Top:=.Top - h, _
Left:=.Left - w, _
Height:=.Height + 2 * h, _
Width:=.Width + 2 * w
ActiveSheet.Ovals. _
Interior.ColorIndex = xlNone
End With
Range("U" & Rw) = Target
End If
Set WatchRange = Nothing
End Sub
Celia
