Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Make Excel always highlight an active cell.

Jan 17, 2005 7:24AM PST

Win XP Pro; Office 2000 (Premium)

I've cussed at this pet peeve for three years.

When using Ctrl-F to find a word or phrase in a worksheet, Excel jumps to the first result but it doesn't highlight it like Word does. Excel just makes the active cell's border fatter, which is no bloody help at all because it's barely noticable.

Is there a way I can change the way Excel displays an active cell? I'd like it to highlight the whole cell with some colour or other or even just black with white font or something. I tried changing the Windows Display properties in Control Panel but that didn't work.

So-o-o...with fingers crossed, I say thanks in advance for any help.

(OhPlease...OhPlease...OhPlease...)

Discussion is locked

- Collapse -
Hi Dover
Jan 18, 2005 9:32PM PST

I've been puzzling over your problem for a couple of days, and I haven't really come up with a solution.

I suspect that you will need some sort of Visual Basic Macro. Something which changes the properties of the cell when it is active, or highlighted. But I'm not a VB man myself.

Microsoft have very good newsgroup site, (browser based), here where you can search for people who had similar questions, or post a question yourself.

Or perhaps swisse, on these forums may pick this up and give you advice. He's a wiz on VB programming.

I have found a method that might work for you, but it's a workaround, and may need careful tweaking and positioning to get it to look right and work well.

It uses Conditional Formatting, (Format > Conditional Formatting).

You can format any cell's properties to change when a condition is met. So, if you set a cell (or group of cells), to point to a target cell, (note, I don't mean the contents of the cells, just the conditional formatting), and format the cell or cells to change background color if the contents of the target cell equals that of the formatted cells, then the color of the formatted cells wil, change.

Example

Cell A1 is the target cell. In this cell you type in the value or expression you want to search for, instead of using the Find function.

Cells B1 to B5 hold the following values;

B1=20
B2=30
B3=Fred
B4=empty
B5="Mary"

Then highlight cells B1 to B5, goto Conditional Formatting, and select Condition 1 "Cell Value is", "equal to", =$A$1, then click the Format button to select how you want the property of the cells to change

You can select Condition 2, (click the "Add" button), to reset the formatting if the target cell is blank, (although it isn't necessary).

In this way, you can use cell A1, (or whatever cell you want), as your Find function, and conditionally format "all" the cells that you need to be highlighted if the contents of A1 matches any of the other cells.

I have just tried a little demonstration and it works, but this may not be what you want.

Let me know if this helps in any way.

Mark

- Collapse -
Here's a solution:
Jan 19, 2005 3:36AM PST
- Collapse -
Search Macro
Jan 19, 2005 5:51AM PST

Hello dover,
Here is a search macro I wrote. The problem is - it highlights only the first found instance of the searched word. Maybe I could improve it later to highlight also other found queries. Anyway, try it, but I presume that you have some working knowledge of macros, and would rather put it in an empty module:

Sub SearchAndHighlight()
Dim ThisArea As Range, SearchMe As String
Sheets("Sheet1").Activate
SearchMe = InputBox("Write here what you're looking for.")
If IsEmpty(SearchMe) Then Exit Sub
Set ThisArea = Sheets("Sheet1").Cells.Find(SearchMe, lookat:=xlWhole)
If ThisArea Is Nothing Then
MsgBox SearchMe & " - " & "There is no such entry."
Else
Cells.Interior.ColorIndex = xlColorIndexNone
ThisArea.Interior.ColorIndex = 6
End If
End Sub

Swisse

- Collapse -
Highlight active cell
Oct 7, 2015 9:47AM PDT

This will highlight the active cell light blue as you move around the sheet. Use Alt+F11 to open VBA, select the sheet of interest on the left and add in the the VBA below. Need to save the workbook as a .xlsm.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False
'Clear the color of all cells
Cells.Interior.ColorIndex = 0
' Highlight the active cell
Target.Interior.ColorIndex = 20
Application.ScreenUpdating = True

End Sub


Cheers

- Collapse -
That's smart.
Oct 9, 2015 2:18AM PDT

If you put it in your personal macro file, it will work in all sheets in all workbooks (which are enabled for macro's). But that might be more than you want.

Kees

- Collapse -
Smart, perhaps
Oct 9, 2015 3:14AM PDT

but I am not sure how usable it would be for the OP in this 2005 discussion.

EG, xlsm files were not available for Office 2000 in 2005.

Poster's really should look and understand whether their replies to long finished discussions are appropriate! Devil

Mark

- Collapse -
Active Cell Outline Color
Nov 12, 2015 11:53PM PST

I am having same problem with having active cell highlight in a different border color. I am working in Excel 2010. I copied and pasted the VB code and choose option to apply to entire workbook. I saved the workbook as an .xlsm document. What I would like to be able to do: (1) tab from cell to cell and have the border around any given cell be a different color than the rest of the gridlines; (2) I do not want to change color of all other gridlines, just want the border around the active cell to be a different color in order to be more visible; (3) Would like to be able to keep any cell shading that already exist in any cell.