Need Help with Excel (Row Highlighting)

ArisaAnsar
ArisaAnsar used Ask the Experts™
on
Hi,
I have an Excel file with over 9000+ rows.  Is there a way when I click in a specific row to update, the entire row is temporary highlighted in another color to let me quickly see all cells that need updating?

The spreadsheet have over 40 columns so highlighting the row, will make it easier to ensure I am updating the correct cells.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
One approach is to use a Worksheet_SelectionChange event sub to select the entire column and entire row when you click on a cell. Those cells will be highlighted in gray so you may easily tell where you are. The code to do this must be put in the code pane for the worksheet being watched, and the file must be saved with either .xlsm or .xlsb file extension.
'This code must go in the code pane of the worksheet being watched
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Union(Target.EntireRow, Target.EntireColumn).Select
Target.Activate
Application.EnableEvents = True
End Sub

Open in new window

Software Team Lead
Commented:
here is another example with conditional formatting:

Auto-Highlight the Active Row When a Cell is Selected
https://www.thesmallman.com/blog/2016/9/15/auto-highlight-the-active-row-when-a-cell-is-selected
29164653.xlsm

Author

Commented:
Ryan - this is very helpful as it allowed me to highlight the row and still continue to use the "Control F" feature.

byundt - for some reason, I couldn't use the "Control F" to search the spreadsheet for specific accounts but I loved how it highlighted both the row and column.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
@Roy

Useful stuffs too, thanks for sharing = )
Roy CoxGroup Finance Manager

Commented:
I've used the first one for years.

The only issue with using Styles is that sometimes if a row contains different Styles then they can be over-written.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial