|
Automatically Highlight the Active or Selected Cell
This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that any cell you select will be highlighted in a way that fills the cell in with color. This will also highlight any selection of cells from one to as many as you select and once you click away the highlight will disappear. This is great for giving presentations of for showing someone how to do something in excel, but only for worksheets with not previously colored cells - see Note.
Note: Using this macro will not allow you to apply background color to any cells in the workbook where this is used. This means that you will not be able to assign colors to particular cells unless you remove the macro. Additionally, any previously colored cells that are clicked on when this macro is being used will lose all color after the active cell moves to another cell. This means you will quickly lose the color for cells in previously colored worksheets.
Where to install the macro: ThisWorkbook
Automatically Highlight the Active or Selected Cell
Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Change the NUMBER in the line of code reading
'"Target.Interior.ColorIndex = 3 " in order to get a
'different color to highlight the active cell.
Static OldRange As Range
On Error Resume Next
Target.Interior.ColorIndex = 3
OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target
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
I am working in excel and having trouble seeing the active cell. I would
like to have the active cell highlighted. Is this possible? It would make
it possible to find the active cell easier.
I am looking for a way to highlight the row of the active cell but maintain the cell position in the row, in the same way if you push SHIFT+SPACEBAR so the row is selected but you are still able to change data in the cell you have marked..
I found a thread thad described a VB script that select the entire row of the selected cell but it dose not keep the cell active it always activate the cell in column A of the selected row
The VB command is: ActiveCell.EntireRow.Select
I also found a way to use Conditional Formating but this dose not work with shared workbooks, and an other way using VB to change the color of all cells in the active row, but this mess up my color definitions in my workbook.
Please Help, it makes me crazy.
Hi,
I would like to know if there was a way to have an active cell always in
yellow or any color.
Example: If the active cell is moved around, the active cell will be always
in yellow.
Thank you in advance
how do I program excel 2010 to highlight the active cell when most of the cells on my spreadsheet have drop down lists and formulas already?
I want to highlighted (in any way) the activecell's complete row from column A till IV.
Like when I move my cursor anywhere, the relevant row highlights from column A till IV.
I am a very advanced Excel user so this is a new one for me.
When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.
In addition:
Can not fill series
Can not change cell format (specifically to show Currency or Accounting w/$ symbol)
And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.
Help!
Hi
Not sure if there is anyway around my problem, but after searching a way to highlight active cells I came across the below code, my problem is my sheets are locked and would prefer it to be this way, so it always shows the debug error message. Is there any other way the cell can be highlighted when searching, basically if theres a list of batches within the colunm such as 1001[1] 1001[2] and 1001[3] can a pop up be made show what ive searched ?
Thanks
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 6
Set OldCell = Target
End Sub
Is there a way to highlight the active cell in excel, so that when you click on a cell it shows up in yellow or some other color? I'm not referring to the option to use the paint bucket to fill the active cell. I want the highlight to disappear from the active cell the another cell after it is no longer the active cell.
Can this be done for all of excel at once or only a workbook?
Hello All,
I am trying to get a form of active cell highlight, but with a slight
diffrence.
I need the cell to remain coloured after it's not the active cell anymore.
So in a nutshell, any cell that is clicked on, will stay the colour when
it's no longer active.
Thanks
Peter
I am working on VB code that will highlight the border of the current active cell in the worksheet.
Here is the code that I have so far:
Private Sub Worksheet_Border(ByVal Target As Range)
Dim iBdrs As Integer
On Error Resume Next
iBdrs = Target.Borders.ColorIndex
If iColor < 0 Then
iBdrs = 4
Else
iBdrs = iBdrs + 1
End If
With Target.Borders
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = iBdrs
End With
End Sub
Obviously, this is not correct. Does anyone know where I need to go from her? I would prefer that after I move the active cell that the border will go back to default. So, there will be no trail of where the active cell has been.
Thanks
|
|
