PDA

View Full Version : Solved: highlighting a row in excel if selected



Jackson25
02-17-2010, 09:03 AM
Hi everybody,

I just wanna get some help about highlighting a row with a VBA code on excel if a cell of that row is selected. I got that code and that works but I don't want the entire row! For example, from A3 to AC3 and only in the rows 3 to 88. Wherever I click, the entire row is highlighted and even the highest row with my titles and the row underneath my tab change color as well. I just want A to AC and row 3 to 88. Is that possible with that formula to change something to get what I want... or I'm gonna need something else or that just impossible.

Thx for your help!


Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc
If cc <> "" Then

With Rows(rr).Interior
.ColorIndex = 50
End With
End If
r = Selection.Row
c = Selection.Column
rr = r
cc = c
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
End Sub

mbarron
02-17-2010, 09:48 AM
Try this one;
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static prevRange As Range

If Not prevRange Is Nothing And prevRange.Row <= 88 And prevRange.Row >= 3 Then prevRange.Interior.ColorIndex = 50

If Not Intersect(Target, Range("a3:ac88")) Is Nothing Then
Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac")).Interior.ColorIndex = 20
End If

Set prevRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac"))


End Sub

Bob Phillips
02-17-2010, 10:11 AM
Take a look at http://www.cpearson.com/Excel/RowLiner.htm

Jackson25
02-17-2010, 11:13 AM
Hi,

Thx for your quick reply but I tried and It doesn't work... There's a bug about that line :

If Not prevRange Is Nothing And prevRange.Row <= 91 And prevRange.Row >= 3 Then prevRange.Interior.ColorIndex = 50

Thx for helping me


Try this one;
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static prevRange As Range

If Not prevRange Is Nothing And prevRange.Row <= 88 And prevRange.Row >= 3 Then prevRange.Interior.ColorIndex = 50

If Not Intersect(Target, Range("a3:ac88")) Is Nothing Then
Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac")).Interior.ColorIndex = 20
End If

Set prevRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac"))


End Sub

SamT
02-17-2010, 11:31 AM
Put the curser in the word "Not" on your code page and hit the F1 key.

SamT

Bob Phillips
02-17-2010, 12:25 PM
TRy this



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Static prevRange As Range

If Not prevRange Is Nothing Then

If prevRange.Row <= 88 And prevRange.Row >= 3 Then prevRange.Interior.ColorIndex = xlColorIndexNone
End If

If Not Intersect(Target, Range("a3:ac88")) Is Nothing Then
Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac")).Interior.ColorIndex = 20
End If

Set prevRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "ac"))
End Sub

Jackson25
02-17-2010, 01:38 PM
Thx everybody for your help, it was my first visit on that website and it's helpful and very very quick. So thx a lot, it has been awesome guys!