Highlighting Active.Row

Viktor86HUN

New Member
Joined
Dec 4, 2014
Messages
17
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:
Code:
[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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I'm using these in Tandem
Code:
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
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
*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...

Code:
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.
 
Upvote 0
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"
 
Upvote 0
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 ;)
 
Upvote 0
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.
 
Upvote 0
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 :(
 
Upvote 0
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)?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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