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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

End Sub

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.

[mole999]

I'm using these in Tandem

Public Sub highlightFormaulas()
    With Sheets("data").Range("C:AQ")
        'ActiveSheet.UsedRange
        .SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = 35    '43 '6
    End With
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        '.EntireRow.Interior.ColorIndex = 6 '8
        .EntireRow.Interior.ColorIndex = 6    '8
        '.EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
    highlightFormaulas
End Sub

as you say it will change any manually applied highlight, but if they are applied with CF then when you change focus the other highlights return

[Viktor86HUN]

Hey mole.

Thanks for your reply
Unfortunately your code gives me "No cells were found" error
It works a bit, so i can see that it removes all other highlighting. This is what i'm trying to avoid.
The code i've pasted would do this, if it could work. Any chance you see what's the problem with it?

*EDIT
My sheet is very colorfull, and wouldn't want to re apply those formatting.
Also trying to find the best code, which takes the less performance.
My 2. code if it could be solved, would be great

[Rick Rothstein]

*EDIT
My sheet is very colorfull, and wouldn't want to re apply those formatting.
Also trying to find the best code, which takes the less performance.
My 2. code if it could be solved, would be great

It depends how colorful your sheet is as to whether this will work for you or not. What the procedure does is color only the uncolored cells in the active row yellow. so if you do not have a lot of uncolored cells, you will not be able to see the highlight very well. Anyway, this workbook (not worksheet) event code, so find the item labeled "ThisWorkbook" in the VB editor's Project list window and double click it to open its code module, then copy/paste this code into it...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  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
End Sub

That's it, go to any worksheet and click a cell.

[mole999]

Hey mole.

Thanks for your reply
Unfortunately your code gives me "No cells were found" error

should have been installed on the worksheet, its triggered by clicking around the target sheet only, so shouldn't be able to say it found nothing. This was on the back of sheet called "data"

[Viktor86HUN]

Mr Rothstein. That code is great, makes exactly what i want.
Only problem with it, is it's performance.
Which i managed to solve my disabling Application.Screenupdating.
Now works like a charm.
Thank you Roth, you made my day

[Rick Rothstein]

Only problem with it, is it's performance.
Which i managed to solve my disabling Application.Screenupdating.
Now works like a charm.

I guess that is because your sheet has a lot of cells filled in. I never really tested it with a crowded worksheet, so thanks for the feedback on that... I will modify my archive of that function to include turning off screen updating.

[Viktor86HUN]

Is there a way to apply this function to a checkbox?
if it check the function could work, but would be nice to have the feature to disable it
Im not very familiar with checkboxes and stuff

[Rick Rothstein]

Is there a way to apply this function to a checkbox?
if it check the function could work, but would be nice to have the feature to disable it
Im not very familiar with checkboxes and stuff

Where would the checkbox be located at? The way the event code is setup, it works on every sheet of the workbook... is that what you need or did you want it restricted to a single worksheet (which would then, obviously, house the checkbox)?

[mick0005]

This is great stuff! I used to have an addin a few years ago where I found this very helpful, but it also always cleared the previously applied formatting so I seldom used it. This is a treat!