×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Add-In to highlight entire row/column of selected cell in Excel?
3

Add-In to highlight entire row/column of selected cell in Excel?

Add-In to highlight entire row/column of selected cell in Excel?

(OP)
Anyone know of an Add-In (or something else) for Excel that will highlight entire row and column of selected cell, instead of just the row and column headers?  I think I saw this in Lotus.  It'd be nice to have that feature in Excel.  I wrote a macro to do it, but the only way to highlight the rows and columns I could think of was to select them...and then that mess's with which cell is selected because they're all selected...and when I hit the Delete key, *all* those cells were deleted :(

Thanks for your time,
Ken

RE: Add-In to highlight entire row/column of selected cell in Excel?

2
You can use conditional formating features and specify following two conditions, for entire sheet cells, with appropriate cell format
Condition 1

Formula Is =ROW()=activerow()

Condition 2

Formula Is =Column()=activecol()

whre ActiveRow and ActiveCol are two functions defined in a module as

Function ActiveRow()
ActiveRow = ActiveCell.Row
End Function
Function ActiveCol()
ActiveCol = ActiveCell.Column
End Function

To apply this formating each time you select a cell write following code in SelectionChange event of the worksheet as

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.Calculate
End Sub

This however will suppress your existing cell formats temporarily when highlighting the row and column
I can give you the sample if required
Hope this will help

RE: Add-In to highlight entire row/column of selected cell in Excel?

The method above forces excel to execute a macro every time you move the cursor. It means no UNDO or even COPY command will be available... Too much of a price to pay... Once I wrote the following routine for company timesheet:


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim irow%
On Error Resume Next
irow = Target.row
Range("A1:T40").Interior.ColorIndex = xlNone
Cells(irow, 2).Resize(1, 19).Interior.ColorIndex = 36

End Sub

It does the work, but disadvantages I described still apply.

RE: Add-In to highlight entire row/column of selected cell in Excel?

No these disadvantages are not there. However, I would be better to modify the code as under
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim WinState, MyWin As Window

ActCol = Target.Column
ActRow = Target.Row
Set MyWin = ThisWorkbook.Windows(1)
Application.ScreenUpdating = False
WinState = MyWin.WindowState
MyWin.WindowState = xlMinimized
MyWin.WindowState = WinState
Application.ScreenUpdating = True

End Sub

Public ActCol, ActRow

Function ActiveRow()
ActiveRow = ActRow
End Function

Function ActiveCol()
ActiveCol = ActCol
End Function


A sample can be downloaded from
 
http://home.ripway.com/2004-6/135145/Public/HighlightSelectedRowColumn.xls

In both the original code and modified code, the COPY and UNDO features are available

RE: Add-In to highlight entire row/column of selected cell in Excel?

Thanks! Your code works great, undo and copy command work as well. The only complain, the windows blink performing Application.ScreenUpdate.

RE: Add-In to highlight entire row/column of selected cell in Excel?

(OP)
tmukhtar,
Thanks!  I'll give it a try.
Ken

RE: Add-In to highlight entire row/column of selected cell in Excel?

tmukhtar: The XLS url no longer works, could you re-post it somewhere please?

Eric

RE: Add-In to highlight entire row/column of selected cell in Excel?

all,

you really do not need to write vba code to highlight or shade alternate rows. note the tip from the following website:

http://www.j-walk.com/ss/excel/usertips/tip043.htm

good luck!
-pmover

RE: Add-In to highlight entire row/column of selected cell in Excel?

Whoops PMover,

I think you missed the concept. We are trying to highlight a cell, column, row, etc... when another cell is selected.

For me, I created a vacation calendar with dates going down the left and people's names across the top.  When a person selects a cell somewhere in the middle of the spreadsheet, I want the name cell on the top and the date cell on the left to be selected to show where they are (like an X Y coordinate).  In the example below, the person clicked on the cell under Bob and on January 3rd, I would like Bob's name to highlight and January 3rd to highlight.

       Eric  Sally  BOB  Robert Pam  Mary   ....
jan 1
jan 2
JAN 3               XXX
jan 4
jan 5
....

After a while last night, I WAS able to use tmukhtar example and get it working.  When I get home tonight, I'll post my implementation of his code for others to reference.

Eric

RE: Add-In to highlight entire row/column of selected cell in Excel?

thanks Eric! whoops is right . . . i oft glance over text (reading too much at times) and not read the details. shame on me . . .

thanks again!

-pmover

RE: Add-In to highlight entire row/column of selected cell in Excel?

You can also try:

Union(ActiveCell.EntireRow, ActiveCell.EntireColumn).Select

TTFN



RE: Add-In to highlight entire row/column of selected cell in Excel?

This code is the same concept but does not need conditional formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Len(ActCol) > 0 Then         'Not first time in worksheet
       Cells(ActRow, ActCol).EntireRow.Interior.ColorIndex = xlColorIndexNone
       Cells(ActRow, ActCol).EntireColumn.Interior.ColorIndex = xlColorIndexNone
    Else                            'First time in worksheet
       Range("A1:IV65536").Interior.ColorIndex = xlColorIndexNone  'Clear colorindex of entire worksheet
    End If
    
    ActRow = Target.Row
    ActCol = Target.Column
    
    Cells(ActRow, ActCol).EntireRow.Interior.ColorIndex = 19    '19 is light yellow
    Cells(ActRow, ActCol).EntireColumn.Interior.ColorIndex = 19

End Sub

Public ActCol, ActRow

RE: Add-In to highlight entire row/column of selected cell in Excel?

Simplification of above post.  Also, when using code from first post, I was getting slow response once I updated data in a cell.  This approach does not use conditional formatting and does not affect the speed after updates.  Also it does not require a separate module for the ActRow and ActCol public variables:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Clear interior color for entire worksheet
    Range("A1:IV65536").Interior.ColorIndex = xlColorIndexNone
    
    'Set row/column interior color
    Cells(Target.Row, Target.Column).EntireRow.Interior.ColorIndex = 19    '19 is light yellow
    Cells(Target.Row, Target.Column).EntireColumn.Interior.ColorIndex = 19
    
End Sub

RE: Add-In to highlight entire row/column of selected cell in Excel?

Simplification of above post.  Also, when using code from first post, I was getting slow response once I updated data in a cell.  This approach does not use conditional formatting and does not affect the speed after updates:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Clear interior color for entire worksheet
    Range("A1:IV65536").Interior.ColorIndex = xlColorIndexNone
    
    'Set row/column interior color
    Cells(Target.Row, Target.Column).EntireRow.Interior.ColorIndex = 19    '19 is light yellow
    Cells(Target.Row, Target.Column).EntireColumn.Interior.ColorIndex = 19
    
End Sub

RE: Add-In to highlight entire row/column of selected cell in Excel?

Last post on 22 Aug 05 14:03 by bfrkhjk works but prevents copying from one cell and pasting to another.

RE: Add-In to highlight entire row/column of selected cell in Excel?

Two alternatives

1. Use script from 22-AUG and copy the value from the forumla entry (or double-click cell, drag mouse over values, press ctrl-c) which uses Windows copy instead of Excel cell copy.

2. Modify script as below.  With this, you need to press ESC to get out of Excel copy mode:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode Then
    Else
       'Clear interior color for entire worksheet
       Range("A1:IV65536").Interior.ColorIndex = xlColorIndexNone
    
       'Set row/column interior color
       Cells(Target.Row, Target.Column).EntireRow.Interior.ColorIndex = 19    '19 is light yellow
       Cells(Target.Row, Target.Column).EntireColumn.Interior.ColorIndex = 19
    End If
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close