Excel Live Cell Help Question

[Texun]

Is there a way to have Excel (2003) highlight the active cell while moving around the worksheet? I'm not talking about a permanent highlight, just a highlight in the cell while it is active.

I often use Crt+F to find values in large spreadsheets, and since active cells look very similar to all other cells, I usually end up looking all over the spreadsheet or tracking it down with the grid coordinate returned in the search. I could enable an option like this years ago with Quattro Pro but haven't seen it in Excel. The solution cannot be a plug-in because it's a work PC and I do not have admin rights.

Any help would be appreciated. :beer:

 

[mayest]

No way to do that without a macro. However, you might be able to accomplish something similar and maybe better, depending on what you really want. The possible solution that comes to me is the Watch Window. That will allow you to specify one or more cells to appear in a small window. No matter where in the spreadsheet you go, or what you click on, it will be visible. The Watch Window shows the workbook name, the sheet name, the cell's current value (updates in real time automatically), and the formula. I think that pretty much covers the reasons that you would want the cell selected. If not, let me know why you want to do this.

You can access the Watch Window through the Formula Auditing tool bar (View --> Toolbars and select Formula Auditing), or Tools --> Formula Auditing --> Show Watch Window.

 

[Texun]

Thanks. You are close but I may not have phrased my question correctly so I'll add some links to a sample of something similar to what I am looking for.

I have This and I need to go to something like this. The latter would be better but I was hoping to have the active cell light up with a color instead of the boarder as shown. A colored boarder is easier to see but it would be great to have it highlight only while the cell was active. Finding the active cell in a large worksheet during multiple searches would be a snap if I could get it to highlight in something like green or yellow.

 

[mayest]

I guess I'm stumped, and still don't quite understand. I'm thinking that you have an active cell, but it is no longer visible on screen because you have scrolled elsewhere. Is that correct? Now, you want to get back to that cell? There may be a keyboard shortcut to make that cell visible, but I don't know what it is. I did discover that if you do and Edit --> Go To (or press F5) and then click the Special button, the active cell will appear on the screen. This may be just a quirk, but at least you would get taken to the part of the screen that contains the active cell.

Another way would be to use a macro to make it visible again:

Public Sub ShowActiveCell()
ActiveCell.Select
End Sub

That, combined with the colored column and row headers should make it easy to find.

If the active cell border must be a different color to make it easier to see, I don't know how to do that. I'm not even sure that VBA can change that. However, if you turn off the grid lines (Tools --> Options --> View tab and uncheck Gridlines) then the active cell border will really stand out.

 

[Texun]

I may not be using the precise terminology. My Excel sheet is white (default) and I have many spreadsheets with hundreds of populated cells.

To search for specific data in a cell I use Ctr+F, enter what I am looking for and BAM! There it is... somewhere among the other hundred or so other cells is my active cell displaying the results, surrounded a very faint boarder. It's difficult to see, since by default the active cell is the same as all other cells. If I only needed it once it wouldn't be a problem, but when doing multiple searches it can be a pain and a strain to spot that little white border among the other cells after each search. I guess could search for border effects and perhaps find an option to bold or color the border to make it stand out among the others.

Quattro Pro used to have an option where the user could select a highlighted color for an active cell. Each press of the key or click of the mouse would highlight the new location with the user defined highlighted color instantly. That was one of the most useful user definable features of QP and I am surprised Excel doesn't support it, at least not that I know of.

Many thanks for the tips. :beer: I'm still looking so if you find anything close please let me know.

 

[mayest]

OK, one more try. 😀 You want the border to flash, or something, to make it visible. As far as I know, that feature isn't built in to Excel.

This won't work while the Find dialog is still visible, so you will have to dismiss it. Immediately after that, you can run this macro to make the border of the active cell red and thick for 2 seconds. It will then reset to its original values. Hopefully, that is enough of a visual cue.

Public Sub HighlightActive()
Dim LS As Integer
Dim W As Integer
Dim C As Integer
'Get original values
With Selection.Borders
LS = .LineStyle
W = .Weight
C = .ColorIndex
End With
'Set to red and thick for 2 seconds
Selection.BorderAround ColorIndex:=3, Weight:=xlThick
Application.Wait (Now + TimeValue("0:00:02"))
'Reset border to original values
With Selection.Borders
.LineStyle = LS
.Weight = W
.ColorIndex = C
End With
End Sub

Copy that to a new module in your workbook (or Personal.xls if you want it available for any workbook). Assign a shortcut key (like Ctrl+Shift+H) and then any time you press that key combination the active cell will be briefly highlighted. You can change the time to any value that works for you.