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

[brengine]

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

 

[tmukhtar]

You can use conditional formating features and specify following two conditions, for entire sheet cells, with appropriate cell format
Condition 1
[COLOR=red yellow]
Formula Is =ROW()=activerow()
[/color]
Condition 2
[COLOR=red yellow]
Formula Is =Column()=activecol()
[/color]
whre ActiveRow and ActiveCol are two functions defined in a module as
[COLOR=red yellow]
Function ActiveRow()
ActiveRow = ActiveCell.Row
End Function
Function ActiveCol()
ActiveCol = ActiveCell.Column
End Function
[/color]
To apply this formating each time you select a cell write following code in SelectionChange event of the worksheet as
[COLOR=red yellow]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveCell.Calculate
End Sub
[/color]
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

 

[yakpol]

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.

 

[tmukhtar]

No these disadvantages are not there. However, I would be better to modify the code as under
[COLOR=red yellow]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
[/color]

A sample can be downloaded from

[URL unfurl="true"]http://home.ripway.com/2004-6/135145/Public/HighlightSelectedRowColumn.xls[/url]

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

 

[yakpol]

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

 

[brengine]

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

 

[SemiConGuy]

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

Eric

 

[pmover]

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

 

[SemiConGuy]

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

 

[pmover]

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

 

[IRstuff]

You can also try:

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



TTFN

 

[bfrkhjk]

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

 

[bfrkhjk]

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

 

[bfrkhjk]

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

 

[JohnAR]

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

 

[bfrkhjk]

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