Make Row outline them selfs

Posted on 2013-09-15
Medium Priority
Last Modified: 2013-10-06
hard for me to describe what I want, but I will give it a shot

I am an accountant. Frequently I make a spread sheet, and need to do a "write up" as we call it.  I make columns with Descriptions. then we duplicate the amount in the " amount Column"  into one of the descriptive columns we made....
the result is that we are able to add up  all columns, and they agree in total to the amount column.

My problem is that as the work sheet gets large, I lose track of which row the number was originally in.  
is there a way that the whole row, could show itself or highlight... just by me clicking on any cell of it.?
Question by:intelogent
  • 2
  • 2
LVL 29

Accepted Solution

IrogSinta earned 2000 total points
ID: 39495121
Microsoft Dev Center explains how to highlight both row and column of an active cell in this link.  What the code does is clear all the colors in all the cells of the worksheet and then highlight a single row and column.  In your case of highlighting just the row, you would basically add vba code similar to this:
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
        Cells.Interior.ColorIndex = 0
        Target.EntireRow.Interior.ColorIndex = 36
    Application.ScreenUpdating = True

Open in new window

While the above works, I prefer using Conditional Formatting to do this.  One benefit here is that it avoids flickering (though maybe this would not be an issue on faster CPUs).  Here are the steps to try this route:


Select Cell A1

2. Select All Cells

Do this by clicking on the upper left corner.Select all cells

3. Add Conditional Formatting

Select New Rule.
For the Rule Type, select "Use a formula to determine which cells to format"
Add the following formula : =CELL("row")=CELL("row",A1)
Select your formatting colorConditional Formatting

4. Add Code to Recalculate

Using the above steps, if you click on a cell and then press F9 to recalculate, you will see your row highlighted.  In order to make the recalculation automatic, you need to add the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Open in new window


Author Comment

ID: 39495828
so will the row now longer call attention to it self once a cell is no longer selected on it.?
LVL 29

Expert Comment

ID: 39495946
If you select a cell in another row, the new row will be highlighted.

Author Closing Comment

ID: 39550678
perefect and thanks

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question