Highlighting row where you are

Bethiepooh · Aug 18, 2002

I am interested in finding out how I can highlight the row I am currently working in? When I move rows, I want the highlighting to move to the appropriate row. How do you do this?

aldo · Jan 24, 2003

Thanks Nate.

You definitely put me on the right track. I opted to use the following if. . .then statement:

Code:

     If ActiveWorkbook.Name = "MyFileName.xls" Then 'replace MyFileName with your file's name

Below is the completed working code. This is an entirely unobtrusive highlighting routine which highlights the entire row while allowing entries to be keyed in. It only highlights when the cursor keys are used to move around the sheet. Unlike the highlight routine originally posted in this thread, this adds the following advantages:

1) It does not disable the undo button. Therefore if you made a huge delete or paste error and then cursored around the sheet you can still go back and undo the error at any time.

2) You can copy and paste by selecting with the mouse

3) Unconditional formats which are removed by the former program remain intact.

Place this code in the Worksheet module you wish to incorporate highlighting (e.g. Sheet1)

Code:

Private Sub Worksheet_Activate()

    Application.OnKey "{RIGHT}", "HighlightRight"

    Application.OnKey "{LEFT}", "HighlightLeft"

    Application.OnKey "{UP}", "HighlightUp"

    Application.OnKey "{DOWN}", "HighlightDown"

End Sub

Private Sub Worksheet_Deactivate()

    Application.OnKey "{RIGHT}"

    Application.OnKey "{LEFT}"

    Application.OnKey "{UP}"

    Application.OnKey "{DOWN}"

End Sub

Place this code into your macro module (e.g. module1):

Code:

Sub HighlightRight()

On Error Resume Next

    x = ActiveCell.Column

    y = ActiveCell.Row

    Range("a" & y, "iv" & y).Select

    Range(Cells(ActiveCell.Row, x + 1), Cells(ActiveCell.Row, x + 1)).Activate

End Sub

Sub HighlightLeft()

On Error Resume Next

    x = ActiveCell.Column

    y = ActiveCell.Row

    Range("a" & y, "iv" & y).Select

    Range(Cells(ActiveCell.Row, x - 1), Cells(ActiveCell.Row, x - 1)).Activate

End Sub

Sub HighlightUp()

On Error Resume Next

    x = ActiveCell.Column

    y = ActiveCell.Row

    Range("a" & y - 1, "iv" & y - 1).Select

    Range(Cells(y - 1, x), Cells(y - 1, x)).Activate

End Sub

Sub HighlightDown()

On Error Resume Next

    x = ActiveCell.Column

    y = ActiveCell.Row

    Range("a" & y + 1, "iv" & y + 1).Select

    Range(Cells(y + 1, x), Cells(y + 1, x)).Activate

End Sub

Place the following code into the 'ThisWorkbook' module if you wish to only have the highlighting occur only within the one workbook when other workbooks are open:

Code:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)

If ActiveWorkbook.Name = "MyFileName.xls" Then 'replace MyFileName with your file's name

    Application.OnKey "{RIGHT}", "HighlightRight"

    Application.OnKey "{LEFT}", "HighlightLeft"

    Application.OnKey "{UP}", "HighlightUp"

    Application.OnKey "{DOWN}", "HighlightDown"

End If

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

    Application.OnKey "{RIGHT}"

    Application.OnKey "{LEFT}"

    Application.OnKey "{UP}"

    Application.OnKey "{DOWN}"

End Sub

-aldo

NateO · Jan 24, 2003

Howdy aldo, you're welcome, glad and to be of help.

Are you nominating yourself for the HOF?

Originally, I had a worksheet test in the following:<pre>
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If ActiveWorkbook.Name = "MyFileName.xls" Then 'replace MyFileName with your file's name
Application.OnKey "{RIGHT}", "HighlightRight"
Application.OnKey "{LEFT}", "HighlightLeft"
Application.OnKey "{UP}", "HighlightUp"
Application.OnKey "{DOWN}", "HighlightDown"
End If
End Sub</pre>

So that if you activated the workbook, and the correct sheet is active, then the procedures get assigned. Me thinks you'll assign the procedures independant of whether you're on the correct sheet or not... The procedures posted last night worked fine under my tests with multiple workbooks open...

Also keep in mind, that the event only fires in the workbook is located in. If you hardcode the name, it won't fire correctly when you save the file as something else. It's not necessary and creates risk.

Have a nice weekend.

_________________
Cheers, Nate Oliver
This message was edited by NateO on 2003-01-24 17:52

aldo · Jan 24, 2003

The ActiveSheet.Index seems to work okay now, but I wasn't sure how to determine the index number. I used 'msgbox ActiveSheet.Index' to arrive at 2 and then did the following edit:

<pre>If ActiveSheet.Index = 2 Then

Application.OnKey "{RIGHT}", "HighlightRight"

Application.OnKey "{LEFT}", "HighlightLeft"

Application.OnKey "{UP}", "HighlightUp"

Application.OnKey "{DOWN}", "HighlightDown"

End If

End Sub</pre>

I just don't know an easy way to determine the index number because I don't know what it is. It seems to work fine now and my file name does incorporate the date so this will mean I don't have to go in and alter the code each time I save the file under a new name.

I guess with that you stole the HOF out from under me.

You Have a nice weekend too. And thanks again for all of your help.

Ivan F Moala · Jan 25, 2003

NateO

Great work!!.....I have amended code for
alternate use to the CF routine.
I'll keep this routine as an Alternate and
post to my site with Credits of course

Option Explicit
'/////////////////////////////////
'// Original by NateO for aldo ///
'// 24th Jan 2003 ///
'// Amended by IFM ///
'/////////////////////////////////

'// Placed in a Std Module
Dim strCol As String
Dim iCol As Integer
Dim dblRow As Double

Sub HighlightRight()
HighLight 0, 1
End Sub

Sub HighlightLeft()
HighLight 0, -1
End Sub

Sub HighlightUp()
HighLight -1, 0, -1
End Sub

Sub HighlightDown()
HighLight 1, 0, 1
End Sub

Sub HighLight(dblxRow As Double, iyCol As Integer, Optional dblZ As Double = 0)
'// Amended to highlight Activecell cross intersection
'// Amended as an Alternative to using Condtional Formats
'// As per Aldo thread;
'// http://www.mrexcel.com/board/viewtopic.php?topic=19239&forum=2&start=20&22
On Error GoTo NoGo

strCol = Mid(ActiveCell.Offset(dblxRow, iyCol).Address, _
InStr(ActiveCell.Offset(dblxRow, iyCol).Address, "$") + 1, _
InStr(2, ActiveCell.Offset(dblxRow, iyCol).Address, "$") - 2)
iCol = ActiveCell.Column
dblRow = ActiveCell.Row

'// If you don't want screen flicker
Application.ScreenUpdating = False

With Range(strCol & ":" & strCol & "," & dblRow + dblZ & ":" & dblRow + dblZ)
.Select
'// Need to reset here!
Application.ScreenUpdating = True
.Item(dblRow + dblxRow).Activate
End With

NoGo:

End Sub

'// Placed in the ThisWorkbook Object
Private Sub Workbook_Open()
Application.OnKey "{RIGHT}", "HighlightRight"
Application.OnKey "{LEFT}", "HighlightLeft"
Application.OnKey "{UP}", "HighlightUp"
Application.OnKey "{DOWN}", "HighlightDown"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{RIGHT}"
Application.OnKey "{LEFT}"
Application.OnKey "{UP}"
Application.OnKey "{DOWN}"
End Sub
'
'EDIT: There is a slight screenflicker when
'scrolling...Set ASU = False if Req.
EDIT: There is a slight screenflicker when
scrolling...Set ASU = False if Req.

_________________
Kind Regards,
 Ivan F Moala From the City of Sails

majinfaisal · Jan 25, 2003

I used the Hall of Fame code from IVAN - and thx to mrxcels visitors i got a toggle button which when pressed upon disables/enables the column-row colouring.

Heres the code:
-----------------------------------
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
ToggleButton1.Caption = " Turn Highlite On"
Cells.FormatConditions.Delete
Else
ToggleButton1.Caption = "Turn Highlite Off"
End If
End Sub
--------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 44
Else
iColor = 44
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = 44

Cells.FormatConditions.Delete

If ToggleButton1.Value = False Then

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

End If

End Sub
---------------------------------------

I dunno if this helps but you may want to try it its up to you.

aldo · Jan 27, 2003

Very nice Ivan.

I knew someone could do better code than what I had devised. (I am a very, very amateur tinkerer.) But what I was truly happy to see was the more defined active cell that you have coded in and I was blown away by the fact that you were able to incorporate the vertical highlight simultaneous to the horizontal using this highlight technique.

You are the master.

Thank you very much.

(I almost believe that this type of highlighting should be hard coded into excel as a general, edit, or view option)

NateO, Ivan definitely gets my vote for HOF on this one. A guy can never have enough Can Koozies.

aldo · Jan 27, 2003

Ivan,

Watching all that data disappear is a little hairy when you absent mindedly hit the 'delete' key while in the highlight mode, but the undo will bring it back so the following may not be necessary. I added the following code to your code which allows you to use the 'delete' key to clear the target cell. The delete key initially removes the highlights and selects the target cell and the subsequent pressing of the 'delete' key deletes the contents. The undo history remains intact. For deleting a range, use the mouse to highlight the range to be deleted and then hit the delete key while delete is enabled. Subsequent curser movement returns you to the highlight mode.

Added this line into the 'Highlight' subroutine:<pre> Application.OnKey "{DEL}", "DisableDelete"</pre>
And this subroutine into the macro module:<pre> Sub DisableDelete()
Cells(ActiveCell.Row, ActiveCell.Column).Select
Application.OnKey "{DEL}"
End Sub</pre>
It's a little awkward, but it does give you the delete function while you are coursing (I don't think cursoring is a word?) around the spreadsheet with the cursor keys.

-Aldo
This message was edited by aldo on 2003-01-27 19:50

Ivan F Moala · Jan 27, 2003

On 2003-01-27 12:57, aldo wrote:
Ivan,

Watching all that data disappear is a little hairy when you absent mindedly hit the 'delete' key while in the highlight mode, but the undo will bring it back so the following may not be necessary. I added the following code to your code which allows you to use the 'delete' key to clear the target cell. The delete key initially removes the highlights and selects the target cell and the subsequent pressing of the 'delete' key deletes the contents. The undo history remains intact. For deleting a range, use the mouse to highlight the range to be deleted and then hit the delete key while delete is enabled. Subsequent curser movement returns you to the highlight mode.

Added this line into the 'Highlight' subroutine:<pre> Application.OnKey "{DEL}", "DisableDelete"</pre>
And this subroutine into the macro module:<pre> Sub DisableDelete()
Cells(ActiveCell.Row, ActiveCell.Column).Select
Application.OnKey "{DEL}"
End Sub</pre>
It's a little awkward, but it does give you the delete function while you are coursing (I don't think cursoring is a word?) around the spreadsheet with the cursor keys.

-Aldo
This message was edited by aldo on 2003-01-27 19:50

Excellent.........will amend with your name
to it and post to my site....good work!!

Edit:
Aldo...it may pay to pu in

Sub DisableDelete()
Cells(ActiveCell.Row,ActiveCell.Column).clearcontents
End Sub

_________________
Kind Regards, Ivan F Moala From the City of Sails

This message was edited by Ivan F Moala on 2003-01-27 22:13

tusharm · Jan 27, 2003

On 2002-08-18 20:04, Bethiepooh wrote:
I am interested in finding out how I can highlight the row I am currently working in? When I move rows, I want the highlighting to move to the appropriate row. How do you do this?

Just in case this escaped notice, XL has a native way to highlight the row and column of the active cell. In XL97 and 2000, the row and column id are raised and shown in bold. In XL2002, the row and column are flagged in a shade of blue. The option to show row and column headings does have to be enabled.

aldo · Jan 29, 2003

On 2003-01-27 21:47, Ivan F Moala wrote:

Edit:
Aldo...it may pay to pu in

Sub DisableDelete()
Cells(ActiveCell.Row,ActiveCell.Column).clearcontents
End Sub

Click to expand...

Won't that clear the undo history? I'm sorry, but I'm exceptionally mistake prone. I live and die by my undo button.
It's too bad vba has to mess with the undo history, cuz I definitely prefer the single keystroke on the code you suggest.
On the other hand, losing the undo history may not be that significant to others and this is your beast now anwyay. I have gotten what I need out of it. She's all yours now.
Thanks again,
-aldo

Highlighting row where you are

Bethiepooh

Board Regular

aldo

New Member

Excel Facts

NateO

Legend

aldo

New Member

Ivan F Moala

MrExcel MVP

majinfaisal

Board Regular

aldo

New Member

aldo

New Member

Ivan F Moala

MrExcel MVP

tusharm

MrExcel MVP

aldo

New Member

Similar threads

Forum statistics

Share this page

Highlighting row where you are

Board Regular

New Member

Excel Facts

Legend

New Member

MrExcel MVP

Board Regular

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock