Highlighting working row in Excel.

Wendy M · Sep 12, 2018

Hi, I searched and tried these directions below. It only highlighted all blank cells in sheet. I want to highlight active row. I couldn't reply, so signed up, the couldn't find thread but I had copied info (below). Please help, so close i think.

We are going to use a formula based approach for Conditional Formating of rows. This is how that would be done ...

1. select the entire worksheet -- you may do this by clicking in the box at the intersection of the row and column headers to the extreme left of the worksheet

2. then invoke FORMAT|Conditional Formating -- in the dialog box that pops up for Condition 1, use

Formula Is ... =cell("row")=row() ... and for pattern choose color of your choice. OK it to close the box.

Now we want to make sure that the coloring of the cells takes place automatically without your having to press any special keys -- and for this we are going to use the following Worksheet_SelectionChange event code ...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

You can install the Worksheet_SelectionChange event code by ...

1. right click on the active worksheet tab

2. in the VBA window that pops up, copy the code listed above in the large white space -- the code window

and then you are ready to roll -- back in the associated EXCEL worksheet, when you select any cell, the entire row will be colored with the color you had specified in Conditional Format.

Does it help? if you need to discuss this further, please post back and then let us take it from there.

My Aswer Is This · Sep 12, 2018

Re: Help highlighting working row in Excel.

Wendy

Please explain in detail what your wanting.

Are you saying you want the entire row highlighted in yellow when you select any cell on that row

So if your active cell is B1 then row 1 interior color will be Yellow
If you then move to C1 row 2 interior color will be Yellow.

Is this what you want?

TotallyConfused · Sep 13, 2018

Re: Help highlighting working row in Excel.

Hello Wendy

First, let me welcome you to this website. You have found a wonderful site that is full of information covering almost any problem I think anyone has ever had. The people here are so helpful. I’m a fairly newcomer to Excel, but I’ve learned more here than anyplace else.

Okay, now to your problem. As I understand your question, you want Excel to highlight the row of any cell you have selected. If this is true, then:
1. Right click on the sheet tab you wish this action to take place on
2. Click on ‘View Code’ in the pop-up box to open the Visual Basic Editor
3. In the drop down box on the left side select ‘Worksheet’
4. In the drop down box on the right side select ‘SelectionChange’
5. Copy and paste the following code into the Visual Basic Editor

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
  Cells.Interior.ColorIndex = 0
  rowNumberValue = ActiveCell.Row
  columnNumberValue = ActiveCell.Column
      For j = 1 To columnNumberValue
         Cells(rowNumberValue, j).Interior.ColorIndex = 37
      Next j
  End Sub

Now go back to your Excel sheet and try clicking on any cell. You should see all cells to the left of your active cell highlighted in BLUE. The number ‘37’ in the code causes the color blue, but that could be changed to whatever color you wish.

In all fairness, I have to admit this is not my code. I ‘stole’ it from a website at
HTTPS//WWW.EXCEL-EASY/VBA/EXAMPLES/HIGHLIGHT-ACTIVE-CELL.HTML
That page also explains how to highlight the column AND row of the selected cell. I simply took out the code to highlight the row.

I hope this helps and again, WELCOME to MrExcel

TotallyConfused

My Aswer Is This · Sep 13, 2018

Re: Help highlighting working row in Excel.

Here is way to do what you want.
I was waiting on your answer to my question but Totally Confused provided a answer and that's great.

Here is another example of his script but a little different:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  9/13/2018  1:09:16 AM  EDT
Cells.Interior.ColorIndex = 0
Rows(Target.Row).Interior.ColorIndex = 37
End Sub

fhqwgads · Sep 13, 2018

Re: Help highlighting working row in Excel.

My Aswer Is This said:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  9/13/2018  1:09:16 AM  EDT
Cells.Interior.ColorIndex = 0
Rows(Target.Row).Interior.ColorIndex = 37
End Sub

TotallyConfused said:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer
  Cells.Interior.ColorIndex = 0
  rowNumberValue = ActiveCell.Row
  columnNumberValue = ActiveCell.Column
      For j = 1 To columnNumberValue
         Cells(rowNumberValue, j).Interior.ColorIndex = 37
      Next j
  End Sub

say you've already highlighted some cells yourself in the sheet and wanted to keep them highlighted in addition to using this script. is that possible?

My Aswer Is This · Sep 13, 2018

Re: Help highlighting working row in Excel.

If you already had some cells highlighted I do not know how you could keep that and use this script also.

Peter_SSs · Sep 14, 2018

Re: Help highlighting working row in Excel.

@ Wendy M
Welcome to the MrExcel board!

(@ fhqwgads Yes, you can keep the formatting - with different code.)

Try this Worksheet_Change code (remove any other Worksheet_Change code from the sheet). This code will retain any existing manual or conditional formatting cell colours (provided it is not ColorIndex 27) and highlight any uncoloured cell(s) in the selected row(s) for the width of the sheet's used range. See if that is suitable for your needs.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error Resume Next
  With Application
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = 27
    .ReplaceFormat.Interior.ColorIndex = xlNone
    Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
    .FindFormat.Clear
    .ReplaceFormat.Clear
    .FindFormat.Interior.ColorIndex = xlNone
    .ReplaceFormat.Interior.ColorIndex = 27
    Intersect(Target.EntireRow, ActiveSheet.UsedRange).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
  End With
End Sub

TotallyConfused · Sep 14, 2018

Re: Help highlighting working row in Excel.

Hi Peter_SSs

Thanks for stepping in here and providing an answer to Fhqwgads question about saving previously selected cell’s format. I had been experimenting with my code trying to figure out a solution and had figured out how to save any previously selected cells. I could also select new cells but only one cell at a time by clicking on it. How to highlight another complete row was still a puzzle.

Whenever I have a few minutes to spare, I come here, load a question someone has asked, and then study the solution that one of you experts offer. I’m hoping that perhaps, and with a great deal of luck, maybe in the next 100 years or so, some of your knowledge will sink into my brain. How you people can remember all the different commands and combinations of commands is beyond me. It just boggles my mind. Lately quite often I’ve been seeing the command ‘UsedRange’ appear as part of solutions, so I need to do some studying on it. So far, my biggest problems have concerned the error messages concerning ‘Objects’ and ‘Properties’ and trying to understand what they are.

I want to take this opportunity to say THANK YOU to you and all the other helpful people on this site. This is a fantastic website, and I’ve learned more here than any other place I’ve been.

TotallyConfused (yes, I still am) [FONT=&quot]&#55357;&#56842;[/FONT]

Peter_SSs · Sep 14, 2018

Re: Help highlighting working row in Excel.

TotallyConfused said:
THANK YOU to you and all the other helpful people on this site.

You're welcome.

TotallyConfused said:
I’ve learned more here than any other place I’ve been.

That is true for me too!

TotallyConfused said:
How you people can remember all the different commands and combinations of commands is beyond me.

It is amazing what can be achieved by saving to Favourites & some searching can do.

Highlighting working row in Excel.

Wendy M

New Member

Excel Facts

My Aswer Is This

Well-known Member

TotallyConfused

Board Regular

My Aswer Is This

Well-known Member

fhqwgads

Board Regular

My Aswer Is This

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

TotallyConfused

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Highlighting working row in Excel.

New Member

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock