Highlighting Active.Row

[Viktor86HUN]

Hey.

Trying to get this solved but strangely it don't work.
I am using Excel 2013.

Ok, i've found 2 solutions so far.

The first one (which is not good for me)
is easy, but the problem with it is that it removes all other colours from the worksheet when working, i've came along some posts where you can actually store your formatting on a seperate sheet, but no this aint gonna be my solution.

The second one.
Looks neat, could use it, it just don't work
https://youtu.be/YSRuGR3q4uw?t=3m35s

Here's what i've done:
-Created new name in name manager: ActiveRow then made it refer to =1
-Applied conditional formatting to the whole sheet: =ROW(A1)=activerow
-Then added the code to Sheet 1:

[COLOR=#0000cd]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With ThisWorkbook.Names("activerow")
    .Name = "activerow"
    .RefersToR1C1 = "=" & ActiveCell.Row
End With

End Sub[/COLOR]

When i click around the sheet i notice some loading, but the highlighting won't happen.
Could you take a look at my issue please? Any other solutions are welcome.

 

[Viktor86HUN]

the function. would be essential only on the main sheet.
So yes i just want to house the checkbox there somehow
I'm experimenting with it already, but cant quite get it to work yet

 

[Viktor86HUN]

I am experimenting with it already, but cant get it to work
The checkbox would be located only on the main sheet, where this function is needed, the other sheets would be nice to remove the highlight feature.

 

[Viktor86HUN]

How do i mark the thread as [Solved] ?
I would like to start a new one for the checkbox issue.

 

[Rick Rothstein]

How do i mark the thread as [Solved] ?
I would like to start a new one for the checkbox issue.

You don't... this forum does not have such an option... just thanking the individual in a response kind of serves that purpose.

By the way, since you only want this functionality for a single sheet, delete the code you installed earlier, right click the tab for the sheet you want the functionality for, select "View Code" from the popup menu that appears and copy/paste the following code into the code window that just opened...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Application.ReplaceFormat.Interior.Color = xlNone
  Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.Color = xlNone
  Application.ReplaceFormat.Interior.Color = vbYellow
  Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.ScreenUpdating = True
End Sub

 

[Viktor86HUN]

Ah, okay,thanking you, and will praise your name through the day

Im still fighting with the checkbox issue though, if you could take a peek at it, i would be happy ^^
http://www.mrexcel.com/forum/excel-...b-workbook_sheetselectionchange-function.html

 

[Raustin]

Hello @Viktor86HUN,
Highlighting selected rows is something i use in a lot of my applications, however it seems much simpler than the solutions above, unless my option does not have the functionality you are looking for. What i like about the feature is that it uses conditional formatting, so not only is the format easy to change at any time, it never will interrupt with your current formatting, even if u are using conditional formatting already within your rows or table. Here is what i use:

Assign a single, perhaps hidden, cell in your worksheet as a selected row #, for our purposes we will use A1. This cell will be used to mark the selected row #. Lets assume your table is B1:E10. Create a new Conditional Format, with a Formula of "$A$1=ROW()". then Format the Cells as you wish. and lastly, in the Applies To Field: "=$B$1:$E$10"
Next Step is the VBA. Add the following VBA Code to the specific Sheet you would like this rule to apply:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B1:E10")) Is Nothing Then
Range("A1").Value = Target.Row
End If
End Sub

Adjust your range accordingly. Please try this and let me know. i would love to hear your feedback, or anyone else who tries it as well.

 

[LikeButtah]

Was wondering about modifying this code to highlight either certain amount of columns (like A:J) instead of the entire row or just one particular column like A.

Code:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.Interior.Color = vbYellow
Application.ReplaceFormat.Interior.Color = xlNone
Cells.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.FindFormat.Interior.Color = xlNone
Application.ReplaceFormat.Interior.Color = vbYellow
Target.EntireRow.Replace "", "", SearchFormat:=True, ReplaceFormat:=True
Application.FindFormat.Clear
Application.ReplaceFormat.Clear
Application.ScreenUpdating = True End Sub