|
Highlight the Row and Column of the Selected Cell
This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell with a solid background color and remove the color when a different cell is selected within the excel spreadsheet. Anytime you select a different cell, the new column and row will be highlighted. This is a great macro for zeroing in on a specific cell during a presentation or just when working in a really big spreadsheet. Also, if you remove the gridlines from the spreadsheet, it can be hard to determine which row or column a cell is in and this will allow you to figure that out much quicker.
This macro will overwrite any previous color in the selected cell, column, and row. That means that if you have a worksheet with a lot of background colors and you use this macro and start selecting cells, the previous background colors will disappear.
To change the color of the highlight change the number in this line of code .ColorIndex = 6.
Where to install the macro: Worksheet
Highlight the Row and Column of the Selected/Active Cell
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc
If cc <> "" Then
With Columns(cc).Interior
.ColorIndex = xlNone
End With
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If
r = Selection.Row
c = Selection.Column
rr = r
cc = c
With Columns(c).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With Rows(r).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Dear All
Whatever cell I select, I want to hightlight the relative row and column of
that cell, how?
Thanks in advance.
Hi,
I want to know if there is any method to highlight the Full Row and full column of the selected cell of the work sheet.
For example, suppose the cell is in C3. I need both Column C and Row 3 to be highlighted or to be filled with any color that I choose (Just think Yellow). Is this possible?
I found some code that will highlight the row and column of a selected cell. However, I can't seem to get it to work. Are you able to get this macro to work, or do you have one simpler? Also, I already have a Worskheet selection change seen below. How do I incorporate that as well?
Highlight Cell VBA:
Option Explicit
'// 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"
Application.OnKey "{DEL}", "DisableDelete"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{RIGHT}"
Application.OnKey "{LEFT}"
Application.OnKey "{UP}"
Application.OnKey "{DOWN}"
Application.OnKey "{DEL}"
End Sub
Option Explicit
'/////////////////////////////////
'// Original by NateO for aldo ///
'// 24th Jan 2003 ///
'// Amended by IFM ///
'// 28th Jan 2003 ///
'// Amended by Aldo ///
'//
'/////////////////////////////////
'// 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://216.92.17.166/board/viewtopic...=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
Sub DisableDelete()
Cells(ActiveCell.Row, ActiveCell.Column).Select
Application.OnKey "{DEL}"
End Sub
Sub ReSet()
Application.OnKey "{RIGHT}"
Application.OnKey "{LEFT}"
Application.OnKey "{UP}"
Application.OnKey "{DOWN}"
End Sub
My current selection change VBA:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Calendar1.Visible Then Calendar1.Visible = False
If Target.Column < 13 Or Target.Column > 14 Or Target.Count > 1 Then Exit Sub
Calendar1.Left = Target.Left + Target.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Calendar1.Value = Date
End Sub
Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
Calendar1.Visible = False
ActiveCell.Select
End Sub
Thanks,
AJ
Hi
I have a row of titles in a grid and I want to set it up so that the question at the head of a column changes colour when a cell in that column is selected. I have found a couple of methods but they would have removed the conditional formatting in the column when activated (there is no conditional formatting in the header) and I don't know enough about VBA to convert the code.
The list of questions is in E3:AH3
The selectable cells are in rows 5 to 6629
Is this possible?
Is there an excel add-in that will allow the highlighting of the row or
column of a selected cell?
Thanks,
LL
----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/comm...lic.excel.misc
Friends and Experts,
My colleguaes and I work with a rather large data entry/archiving Excel 2007 spreadsheet on a daily basis. It contains several columns and rows that will not easily fit on one viewable screen. We have searched for the ability to highlight the entire data set across the row and down the column of the selected cell. This would allow us to navigate with the directional keys quickly and enter data assuring us we are on the correct row and column with a simple visual indication.
I know that the number and letter of the row and column highlight on their own, but what we are needing is for the entire row and column of data to highlight as well.
I have attached an example of what we would like to see.
Does anyone know of a setting/function/method to accomplish this? I have searched around to no avail.
I want to try and set a system where by when a user selects a cell in column S that, column B shows in yellow. ie If I select S20 then B20 shows in yellow. Ideally it would be nice that B only shows highlighted if there is text in it but if thats more complicated then it doesn't matter.
Does anyone have any ideas please?
I have a code here that will highlight the entire row when one of the cell is selected.
Any one can modify this to just the first cell of the selected row? Thanks a lot!
Here's the code:
VB:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
What I would like to do is, if I select cell B7, is to highlight B7:K7, or if I selected B24 then it would highlight B24:K24.
Would someone be able to guide me on this one please
Many thanks
Paul
I have a formula to sellect the lowest value in a row of cells =MIN(A1,A2,A3,A4,A5,A6....) but i would like that lowest cell to become highlighted when selected so i can see a glace which number is being used. Any help would be much appriceated. jk
|
|
