Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
Ross,
I would recommend that you not change the native Excel navigation keys. Why not create a shortcut (OnKey Method) that calls a macro to select/highlight the data? (You can tie the SetupShortcut and DestroyShortcut to the workbook open and close events. Or you can run these however you desire. Also, I chose Ctrl+q as the shortcut below; you can change the shortcut to be something you desire, but Ctrl+q is not a native Excel shortcut). Best, Matthew Herbert Option Explicit Sub SelectCells() Dim strColStart As String Dim strColEnd As String Dim lngRow As Long strColStart = "A" strColEnd = "Z" lngRow = ActiveCell.Row Range(strColStart & lngRow, strColEnd & lngRow).Select End Sub Sub SetupShortcut() Application.OnKey "^q", "SelectCells" End Sub Sub DestroyShortcut() Application.OnKey "^q" End Sub "Ross" wrote: How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
Ross,
Your question is contradictary. To select the entire row use Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.EntireRow.Select end sub to select columns A -Z use Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveCell Range(Cells(.Row, "A"), Cells(.Row, "Z")).Select End With End Sub To instal these right click your sheet tab, view code and paste the code in on the right Mike "Ross" wrote: How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
How about code that puts a border around the current row? Give this a try.
Right click the tab at the bottom of the worksheet you want to have this functionality, select View Code from the popup menu that appears and then copy/paste the following into the code window that opened up... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo Whoops Application.ScreenUpdating = False Cells.Borders.LineStyle = xlLineStyleNone Target.EntireRow.BorderAround Weight:=xlMedium Whoops: Application.ScreenUpdating = True End Sub By the way, this will work for *any* method of moving to a new cell (or range of cells), not just for the arrow keys. -- Rick (MVP - Excel) "Ross" wrote in message ... How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
Perfect!
Thanks Mike "Mike H" wrote: Ross, Your question is contradictary. To select the entire row use Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.EntireRow.Select end sub to select columns A -Z use Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveCell Range(Cells(.Row, "A"), Cells(.Row, "Z")).Select End With End Sub To instal these right click your sheet tab, view code and paste the code in on the right Mike "Ross" wrote: How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select / highlight entire row.
Hi Ross,
Try this in the worksheet Module. Right click the sheet tab and select View Code, copy and paste in the large white area. Select any cell between A2 to Z25 and note the highlighting. Can be modified to a different range size and a color of choice. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Data As Range Dim i As Integer Dim k As Integer i = 1 k = ActiveCell.Column() Set Data = Range("A2:Z25") Data.Interior.ColorIndex = xlNone If ActiveCell.Row < 2 Or ActiveCell.Row 25 Or _ ActiveCell.Column < 1 Or ActiveCell.Column 26 Then Exit Sub End If ActiveCell.Offset(0, -(k - i)). _ Resize(1, 26).Interior.ColorIndex = 35 End Sub HTH Regards, Howard "Ross" wrote in message ... How can I get Excel to select an entire row when I use the "Arrow UP" key or the "Arrow Down" key? For Example, if the cursor has selected Cell A3 and I "Arrow Down" to A4, the cursor would then select the ENTIRE A4 ROW (this spreadsheet has data in some of the A:Z columns (columns are not consistently filled for all cell A4:Z4). This will help the viewer see all of the A4:Z4 cells highlighted (kind of a custom user input aid). Many Thanks Ross |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to highlight an entire row if a certain value is in a cell | Excel Discussion (Misc queries) | |||
Select and delete entire row Excel VBA macro | Excel Programming | |||
why does this macro select the entire worksheet when run? | Excel Discussion (Misc queries) | |||
Highlight entire rows as I scroll? | Excel Discussion (Misc queries) | |||
Highlight the entire row of a selected cell | Excel Programming |