Highlighting Rows

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
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

Well-known Member
Joined
Dec 4, 2009
Messages
583
Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
Code:
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

Board Regular
Joined
Feb 18, 2010
Messages
63
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

Board Regular
Joined
Feb 18, 2010
Messages
63
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

Board Regular
Joined
Feb 18, 2010
Messages
63
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 :/
 

Forum statistics

Threads
1,081,798
Messages
5,361,373
Members
400,629
Latest member
ganeshkhatri

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top