How to highlight Active Cell in Excel using Conditional Formatting.

[omairhe]

Hey Excel Gurus,

I would like to know the formula for highlighting the active cell of my worksheet using Excel's built in Conditional Formatting rules.

Will appreciate and thank you.

 

[DanteAmor]

Put the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
End Sub

In the conditional format, put the following formula:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]=ADDRESS(ROW(),COLUMN())=CELL("address")[/TD]
[/TR]
</tbody>[/TABLE]

This way you can use the Undo command (Ctrl-z)

Try my file

https://www.dropbox.com/s/qsv1racfue6gucl/highlight Active Cell.xlsm?dl=0

 

[pgc01]

@DanteAmor

Do you need the event procedure for the formatting to work?
In excel 2013 I don't.

@omairhe

You specifically say that you want to use conditional formatting.
This means that it will only highlight the cell if the cell is not empty.

 

[DanteAmor]

@DanteAmor

Do you need the event procedure for the formatting to work?
In excel 2013 I don't.

@omairhe

You specifically say that you want to use conditional formatting.
This means that it will only highlight the cell if the cell is not empty.

The Calculate event is not for the format, it is only for updating the sheet.
If the calculate event does not light up immediately, it could be updated by pressing F9.
The cell can be empty or with data or filled with color, even if the cell has another format conditions, it does not matter, the conditional format applies and the cell is illuminated, I recommend you take a look at my file.

 

[pgc01]

I recommend you take a look at my file.

Sorry, I only download xlsx files and so I cannot test.

What I did is to use the conditional formatting formula that you posted in a range and it behaved as I expected.
It works OK with cells that are not empty.
It works the same with and without the event procedure.
As I said I'm using excel 2013.
Hope it works OK for omairhe.

 

[DanteAmor]

Sorry, I only download xlsx files and so I cannot test.

What I did is to use the conditional formatting formula that you posted in a range and it behaved as I expected.
It works OK with cells that are not empty.
It works the same with and without the event procedure.
As I said I'm using excel 2013.
Hope it works OK for omairhe.

The formula works with any cell, empty or non-empty.
I have 2007 and I need the event procedure.

That is the idea, that works for omairhe.

@omairhe
Only one detail was missing. In the conditional format, in Refers to: Select all cells (=$1:$1048576)

 

[omairhe]

The formula works with any cell, empty or non-empty.
I have 2007 and I need the event procedure.

That is the idea, that works for omairhe.

@omairhe
Only one detail was missing. In the conditional format, in Refers to: Select all cells (=$1:$1048576)

I can confirm that formula works with any cell and my requirment is 100% fulfilled. Although the uploaded sheet did not work with my Excel 2019 version but following the steps provided by Dante Amor worked as a charm. Also event procedure is needed, without that it will not highlight the selected cell.

Thank you .

 

[DanteAmor]

I can confirm that formula works with any cell and my requirment is 100% fulfilled. Although the uploaded sheet did not work with my Excel 2019 version but following the steps provided by Dante Amor worked as a charm. Also event procedure is needed, without that it will not highlight the selected cell.

Thank you .

I'm glad to help you. I appreciate your kind comments.

 

[Sandcastor]

Hey, Dante. I stumbled across this post while looking for something entirely different. Love it. Very useful in excel 2016, because it can be hard to see the selected cell when I am focused on a different window. Thanks!

 

[Sandcastor]

Oh, one problem though. selecting the new cell and forcing a Calculate will cancel cut/copy mode. On the bright side i can still use it by taking out the VBA portion and keeping the Conditional formatting portion. Now to keep a cell highlighted for leter reference, i just press F9, and i can see it easily.

 

[DanteAmor]

Oh, one problem though. selecting the new cell and forcing a Calculate will cancel cut/copy mode.

Change this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Calculate
End Sub

By this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
End Sub