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
Thanks for your time,
Ken





RE: Add-In to highlight entire row/column of selected cell in Excel?
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?
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?
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?
RE: Add-In to highlight entire row/column of selected cell in Excel?
Thanks! I'll give it a try.
Ken
RE: Add-In to highlight entire row/column of selected cell in Excel?
Eric
RE: Add-In to highlight entire row/column of selected cell in Excel?
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?
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 again!
-pmover
RE: Add-In to highlight entire row/column of selected cell in Excel?
Union(ActiveCell.EntireRow, ActiveCell.EntireColumn).Select
TTFN
RE: Add-In to highlight entire row/column of selected cell in Excel?
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?
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?
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?
RE: Add-In to highlight entire row/column of selected cell in Excel?
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