PDA

View Full Version : [SOLVED] Help with a range



gsouza
08-16-2005, 04:45 AM
Hi everybody, hope you can help. This is what I have for a code to highlight a cell I select.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("a1:a25")) Is Nothing Then
Cells.Interior.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = 8
End If
End Sub


What I don't want to do is put a range Range("a1:a25")) , if i have data in column "A" and the last row is 5, I want the range that get highlighted to stop at row 5 only. I don't want the range to be longer than the cells in that column to be longer than what is populated. I don't want to have to go back to VBE and have to manually change the range.

I hope this makes sense, thanks in advance for any help I get.

royUK
08-16-2005, 04:55 AM
Try this



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("a1",Range("a65536").End(xlUp)) Is Nothing Then
Cells.Interior.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = 8
End If
End Sub

mdmackillop
08-16-2005, 04:56 AM
Try



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRow As Long
LastRow = [A65536].End(xlUp).Row()
If Not Application.Intersect(Target, Range("a1:a25")) Is Nothing Then
If Target.Row() > LastRow Then
Exit Sub
Else
Cells.Interior.ColorIndex = xlColorIndexNone
Selection.Interior.ColorIndex = 8
End If
End If
End Sub

gsouza
08-16-2005, 05:03 AM
Thanks guys it worked, as always you guys help a great deal. I will mark this solved. I don't think you guys realize how much you help people at there jobs. Thanks again.