Highlighting Rows

[DaveRadford]

Afternoon All,

I am hiding the Headings when my spreadsheet opens. Is there a way in VB or another method that i can higlight the row of the cell i click within a range.

Thanks in Advance.

Dave

 

[dado6612]

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
    Static cc

    If cc <> "" Then
        With Columns(cc).Interior
            .ColorIndex = xlNone
        End With
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If

    r = Selection.Row
    c = Selection.Column
    rr = r
    cc = c

    With Columns(c).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
    With Rows(r).Interior
        .ColorIndex = 20
        .Pattern = xlSolid
    End With
End Sub

It's not my code, i found it here link

 

[MickG]

Hi, Try this:-
When you click any cell within the Range "Rng" then the Cell colours will be removed and the specifc Row you have selected will be highlighted.

Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range("A1:J15")
Rng.Interior.ColorIndex = xlNone
    [COLOR=navy]If[/COLOR] Not Intersect(Target, Rng) [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
        Target.EntireRow.Interior.ColorIndex = 34
    [COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[DaveRadford]

Thanks for the code its quite good. Two thigns i was just looking for the row so i modified it to look like this:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc
If rr <> "" Then
With Rows(rr).Interior
.ColorIndex = xlNone
End With

r = Selection.Row
rr = r
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With

End If
End Sub


Unfortunately it doesnt really meet my needs, some of the cells have different colours in them some grey some yellow and i see that after you click a different cell it will set the fill to none removing the colour. Also i need it within a range such as B11:T44

I was thinking of something along the lines of:

If Range("B11:T11").Click Then
Rows("11:11").Select

But something a little more dynamic and cleaner.

Thanks,

Dave

 

[DaveRadford]

Thanks for the Code MickG, i have modified to suite my needs, check it out:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("A1:J15")
If Not Intersect(Target, Rng) Is Nothing Then
Target.EntireRow.Select
End If
End Sub

that way it will keep my formatting and select the row.

Many thanks for your help

Dave

 

[DaveRadford]

I was a bit ahead of myself, obviously the user is going to to want to type in the box that they have selected however im highlighting the row....i think i may be out of Ideas :/