# Highlight the row you are in to easily see the correct row

#### foweek

##### New Member
Hello:
I posted a question a few days ago received three reponses that sounded all good and all but I could not figure out the solution.

Problem: I would like to find a formula (conditional I think) or a THIRD party software that works with Excel to fix my need.

NEED: I would like to have each row HIGHLIGHTED as I moved up or down in the worksheet so example if I was in CELL J21 the entire row of 21 would be highlighted. As I moved up or down in ROWS the HIGHLIGHTED area would follow the acitive cell/row.

Problem: In my responses from other users they said it was a "PRIVATE-SUB-WORKSHEET" function and gave me some sample formulas to try. I do not know what a PRIVATE SUB WORKSHEET IS. The second thing was I was un-clear where their FORMULAS began and ended. The third thing I was un-sure of is where do I put this formula?

As a new user to MR.EXCEL
I have been reading a lot of problems to see if I could help any too.
I am too new of a user to help anyone yet but I am sure learning.

Thank you
Keith Fowee

Foweek@aol.com
859.581.7232
859.466.6211

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Yogi Anand

##### MrExcel MVP
Re: Highlight the row you are in to easily see the correct r

foweek said:

....Problem: I would like to find a formula (conditional I think) or a THIRD party software that works with Excel to fix my need.

NEED: I would like to have each row HIGHLIGHTED as I moved up or down in the worksheet so example if I was in CELL J21 the entire row of 21 would be highlighted. As I moved up or down in ROWS the HIGHLIGHTED area would follow the acitive cell/row.
....
Keith Fowee

Foweek@aol.com
859.581.7232
859.466.6211
Hi Keith:

I have not seen the other solutions you have refered to -- so my apologies in that regard. However the following should work for you ...

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.

#### foweek

##### New Member
Re: Highlight the row you are in to easily see the correct r

NO, that was perfect
Thank you very much for a quick response
and easy to follow instructions how to make it work.

Happy excel user here.
It does what I want it to do.

Thanks again.

Keith Fowee

#### Yogi Anand

##### MrExcel MVP
Re: Highlight the row you are in to easily see the correct r

Hi Keith:

You Are Very Welcome -- It is nice to see you HAPPY -- now let us keep EXCELing!

#### Ekim

##### Well-known Member
Re: Highlight the row you are in to easily see the correct r

I would like to have each row HIGHLIGHTED as I moved up or down in the worksheet so example if I was in CELL J21 the entire row of 21 would be highlighted. As I moved up or down in ROWS the HIGHLIGHTED area would follow the active cell/row.
Probably the simplest routine that I have seen is the following:
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Cells(ActiveCell.Row, 1).Resize(1, 20).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

End Sub``````
If you want the highlighting in say Sheet1, then copy/paste the above macro to the Sheet1 module (see Yogi’s instructions above).

See this line:

With Cells(ActiveCell.Row, 1).Resize(1, 20).Interior

The active row, columns 1 to 20 will be highlighted. If you want to highlight across say 30 columns, change (1, 20) to (1, 30) etc.

See this line:

.ColorIndex = 36

ColorIndex 36 is a pale yellow, change to suit. To get the index number for all Excel’s 56 colors see:
http://www.geocities.com/davemcritchie/excel/colors.htm

If you want a macro that will place a color chart in a worksheet, post back.

HTH

Mike

#### foweek

##### New Member
Re: Highlight the row you are in to easily see the correct r

Thank you too Mike:

Not sure which way I like better your idea or the previous

They both get the job done.

----------------------------
Question:

is this a formula
This private worksheet function?

If it is a formula? How do you learn it?
like if you are in a cell and you want to do a math problem you can use a algebra like formula to do complete your task but how do you learn all this sub worksheet stuff.

Second is doing this under VIEW CODE
Visual Basic ect.

Is this a MACRO?

--------------------------

Third is how would I try and complete this task.
This is almost so close to DATABASE but it isnt because I want a lot of MATH to be done in these rows and columns

Example:
I want 30 columns
Lets say Price, Description, UPC, and a few others
and I want to access each record by UPC
So I want to hit the UPC and it would auto go to that record (row) and I can make my changes
Then I hit a KEY like F2 and hit auto brings up a INPUT box to let me type in my next upc so I dont have to keep hitting CTRL+F key and make sure I search up and down and then what if the UPC isnt in the spreadsheet I have to go through another step to clear that error message.

I know I am asking alot
I am not expecting you to know how to do this but can it be done?

#### Yogi Anand

##### MrExcel MVP
Re: Highlight the row you are in to easily see the correct r

foweek said:
Thank you too Mike:
Not sure which way I like better your idea or the previous

What I gave you first is primarily a formula based approach -- however I used VBA to create the Worksheet_SelectionChange event for automatic coloring of the rows whenever you make a selection in the worksheet.

What Mike suggested is an all VBA -- macro based approach.

They both get the job done.

isn't that beautiful -- different folks different strokes -- but seriously, you will find there is more than one way of doing the same thing, depending on the context in which we are working, individual preferences, projects constraints, and so on.
And now comes the biggie -- it is controversial -- but it is general understanding that if something can be done without using a macro -- then that's the way to go -- however there are many situations where use of macros is not only prefered but warranted -- so I will leave it at that.

Question:
is this a formula
This private worksheet function?

No, this part of VBA (macro) code

If it is a formula? How do you learn it?
like if you are in a cell and you want to do a math problem you can use a algebra like formula to do complete your task but how do you learn all this sub worksheet stuff.

Second is doing this under VIEW CODE
Visual Basic ect.

Is this a MACRO?

I see you are a bit hazy on differentiating between the worksheet environment and VBA. VBA which is the language for macro programming in EXCEL stands for Visual Basic for Applications -- this is based on one of the very early programming languages BASIC. And whether it is a formula or a macro based approach, it is learnt with patience, perseverence, and practice -- well here are three P's for you.

Third is how would I try and complete this task.
This is almost so close to DATABASE but it isnt because I want a lot of MATH to be done in these rows and columns

Example:
I want 30 columns
Lets say Price, Description, UPC, and a few others
and I want to access each record by UPC
So I want to hit the UPC and it would auto go to that record (row) and I can make my changes
Then I hit a KEY like F2 and hit auto brings up a INPUT box to let me type in my next upc so I dont have to keep hitting CTRL+F key and make sure I search up and down and then what if the UPC isnt in the spreadsheet I have to go through another step to clear that error message.

I know I am asking alot
I am not expecting you to know how to do this but can it be done?
YES -- it can be done.

I hope this helps!

#### rob092

##### New Member
Re: Highlight the row you are in to easily see the correct r

I love both functions, but would love to combine both of them into the same worksheet (kinda overkill, but what the heck).

How would i combine both of these on one sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
With Cells(ActiveCell.Row, 1).Resize(1, 20).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

End Sub

When I try them both, I believe one is overiding the other and as a result, won't let me do either.

#### Ekim

##### Well-known Member
Re: Highlight the row you are in to easily see the correct r

rob092,

If you put both those macros in the same sheet event, you will get the following error message:
Compile error: Ambiguous name detected: Worksheet_Selection Change

The error message occurs because you can only have one “Worksheet_Selection Change” type macro per worksheet, but that macro can do a variety of things. To combine the macros:
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = False

Cells.Interior.ColorIndex = xlNone
With Cells(ActiveCell.Row, 1).Resize(1, 20).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Application.ScreenUpdating = True

End Sub``````
The “Application.ScreenUpdating = False” at the start of a macro simply turns off screen updating as the macro performs it functions. In the context of the above macro it is probably not required. However, if the macro is say copying a very large amount of data from say sheet1 to sheet2, your computer screen will start to flicker as the macro moves between sheet1 and sheet2 and back again. To stop the flickering, you put “Application.ScreenUpdating = False” at the start of the macro (it also makes the macro run faster). Just before the end of the macro, you should turn on screen updating with the following command – “Application.ScreenUpdating = True”

HTH

Mike

Replies
1
Views
471
Replies
0
Views
554
Replies
9
Views
247
Replies
4
Views
313
Replies
8
Views
329

1,186,365
Messages
5,957,438
Members
438,306
Latest member
Crystal_Blue

### 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.

### Which adblocker are you using?

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

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