macro

chazzerman01

New Member
Joined
Nov 18, 2019
Messages
29
does anyone know a macro code that will highlight the whole row that the cursor is on. im using it to make it easier to read values in a table
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
158
Office Version
365, 2013
Platform
Windows
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Application.ScreenUpdating = False
Cells.FormatConditions.Delete
With Target
    With .EntireRow
        .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
        With .FormatConditions(1)
            With .Borders(xlTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
                '.ColorIndex = 5
            End With
            With .Borders(xlBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                '.ColorIndex = 5
            End With
            'row color
        .Interior.Color = RGB(245, 245, 245)
        End With
    End With
   
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
    'cell color
    '.FormatConditions(1).Interior.Color = RGB(255, 255, 0)
End With
Application.ScreenUpdating = True
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
@KOKOSEK, wouldn't that delete any pre-existing conditional formatting as well?
Also, applying CF to an entire row is most likely going to promote file bloat.

Not as good in terms of visibility, but a simpler method
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
    .EnableEvents = False
    .Goto .Union(Target.EntireRow, Target.EntireColumn)
    .EnableEvents = True
End With
    Target.Activate
End Sub
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
158
Office Version
365, 2013
Platform
Windows
Sure it can. I've just pasted macro which I am using in one of mine sheets.

@jason75 - OP does not mention about column.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
Column can be removed if not needed, it was just an additional thought.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,046
@KOKOSEK, wouldn't that delete any pre-existing conditional formatting as well?
Also, applying CF to an entire row is most likely going to promote file bloat.

Not as good in terms of visibility, but a simpler method
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
    .EnableEvents = False
    .Goto .Union(Target.EntireRow, Target.EntireColumn)
    .EnableEvents = True
End With
    Target.Activate
End Sub
I tried your suggestion but did not like all the jumping around. I like solution in post 2
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
158
Office Version
365, 2013
Platform
Windows
Too late to edit post above, so new solution:

Code:
Public OldTarget As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    With OldTarget.EntireRow.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Target.EntireRow.Interior.Color = RGB(255, 255, 0)
    Set OldTarget = Target
End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
I tried your suggestion but did not like all the jumping around. I like solution in post 2
What jumping around? Apart from the addition of the column highlight and a different shade, there is no difference to the behaviour of the 2 methods.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,901
Office Version
2019
Platform
Windows
As I said earier, the column highlight was just an addition that wasn't asked for. It can easily be removed.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
    .EnableEvents = False
    Target.EntireRow.Select
    Target.Activate
    .EnableEvents = True
End With
End Sub
@KOKOSEK Your revised code clears any existing fill colour. :oops:
 

Forum statistics

Threads
1,077,825
Messages
5,336,595
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top