Highlight active cell or selection but restore previous fill color

Worf99 · Aug 12, 2019

Hi guys,
I found this VBA code (here: https://www.extendoffice.com/documents/excel/2129-excel-highlight-active-cell.html)

<code class="vb keyword">Sub</code> <code class="vb plain">Workbook_SheetSelectionChange(</code><code class="vb keyword">ByVal</code> <code class="vb plain">Sh </code><code class="vb keyword">As</code> <code class="vb keyword">Object</code><code class="vb plain">, </code><code class="vb keyword">ByVal</code> <code class="vb plain">Target </code><code class="vb keyword">As</code> <code class="vb plain">Excel.Range)</code>

<code class="vb comments">'Update 20140923</code>

<code class="vb keyword">Static</code> <code class="vb plain">xLastRng </code><code class="vb keyword">As</code> <code class="vb plain">Range</code>

<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>

<code class="vb plain">Target.Interior.ColorIndex = 6</code>

<code class="vb plain">xLastRng.Interior.ColorIndex = xlColorIndexNone</code>

<code class="vb keyword">Set</code> <code class="vb plain">xLastRng = Target</code>

<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>

It works great but if I have cells with the fill color different than white, it sets the fill color to white.
Is there a way to improve the code so that it restores, once I move to another cell, whatever fill color I have in the active cell or selection?

Thank you very much,
Matt

DanteAmor · Aug 12, 2019

Review this

https://www.mrexcel.com/forum/excel...ell-excel-using-conditional-formatting-2.html

Worf99 · Aug 12, 2019

Hi Dante,
first of all thank you for your input.

I downloaded the example file, but nothing happens, ie no cell is highlighted.
I'm sure I'm missing something!

DanteAmor · Aug 12, 2019

Worf99 said:
Hi Dante,
first of all thank you for your input.

I downloaded the example file, but nothing happens, ie no cell is highlighted.
I'm sure I'm missing something!

Check that macros are enabled.
Check that the calculation of formulas is automatic.
Check that the events are active. Run this macro on any module:

Code:

Sub eneve()
Application.EnableEvents = True
End Sub

Jaafar Tribak · Aug 13, 2019

Forcing a calculation upon each selection has a bad impact on performance specially if the workbook contains lots of formulaes.

You can try this alternative :

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    

    With CommandBars.FindControl(id:=30002)
        If Len(.Tag) Then
            Range(.Tag).FormatConditions.Delete
        End If
        Target.FormatConditions.Add(Type:=xlExpression, Formula1:=True).Interior.Color = vbYellow
        .Tag = Target.Address
    End With
    
End Sub

Worf99 · Aug 13, 2019

Hi Jaafar,
thank you so much for your awesome code.
It works great and it keeps previous cell formatting.
The only issue is that if I try it on a protected sheet, I have VBA runtime error 1004.
And I need the sheet to be protected.

DanteAmor · Aug 13, 2019

You checked my file, it works even if the sheet is protected.

Operation is explained in the link. Anyway here I describe it:

Put the following code in the events of your sheet.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
In the conditional format, put the following formula:

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

[/TD]
[/TR]
</tbody>[/TABLE]

In Refers to:

Select all cells (=$1:$1048576)

Worf99 · Aug 13, 2019

Hi Dante.
I checked your file "highlight Active Cell.xlsm".
I don't know how to do this
In the conditional format, put the following formula:
=ADDRESS(ROW(),COLUMN())=CELL("address")

I mean, I went to New Rule but then, where do I write your formula?
Also, do I have to select the cells I want the formula to apply to?

Thank you!

DanteAmor · Aug 13, 2019

Worf99 said:
Hi Dante.
I checked your file "highlight Active Cell.xlsm".
I don't know how to do this
In the conditional format, put the following formula:
=ADDRESS(ROW(),COLUMN())=CELL("address")

I mean, I went to New Rule but then, where do I write your formula?
Also, do I have to select the cells I want the formula to apply to?

Thank you!

To create the first rule:

1. Select all cells.

2. Then, click Home > Conditional Formatting > New Rule.

3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.

4. Under Format values where this formula is true:, type the formula:

=ADDRESS(ROW(),COLUMN())=CELL("address")

5. Click Format.

6. In the Color box, select your favorite color.

7. Click OK until the dialog boxes are closed.

Ready, select any cell.

Worf99 · Aug 13, 2019

Dante, I did it!

I realized I had to translate your formula in Italian: [FONT=&quot]=INDIRIZZO(RIF.RIGA();RIF.COLONNA())=CELLA("indirizzo")
(I thought it was like a VBA code, in which case it was the same even with my Excel italian version)

[/FONT][FONT=&quot]Now it works like a charm!

Muchas gracias de Italia![/FONT][FONT=&quot]
Muchas gracias de Italia!
[/FONT][FONT=&quot]Muchas gracias de Italia![/FONT][FONT=&quot]
[/FONT][FONT=&quot][FONT=&quot]Muchas gracias de Italia![/FONT][FONT=&quot]
[/FONT]

[/FONT]

Highlight active cell or selection but restore previous fill color

Worf99

New Member

Excel Facts

DanteAmor

Well-known Member

Worf99

New Member

DanteAmor

Well-known Member

Jaafar Tribak

Well-known Member

Worf99

New Member

DanteAmor

Well-known Member

Worf99

New Member

DanteAmor

Well-known Member

Worf99

New Member

Similar threads

Forum statistics

Share this page

Highlight active cell or selection but restore previous fill color

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock