macro

chazzerman01

Board Regular
Joined
Nov 18, 2019
Messages
65
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
@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
 
Upvote 0
Sure it can. I've just pasted macro which I am using in one of mine sheets.

@jason75 - OP does not mention about column.
 
Upvote 0
Column can be removed if not needed, it was just an additional thought.
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
Maybe it's just because it's doing the column and the row.
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top