Highlight active cell or selection but restore previous fill color

[Worf99]

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

 

[Jaafar Tribak]

I have the 2007 version of Excel, so it is necessary to use the Calculate instruction, but in 2013 and later it is not necessary, delete the event procedure and perform a test.

I use excel 2016 and it is necessary to force a calculation otherwise the screen doesn't refresh.

If I use Application.ScreenUpdating=True then it works and avoids having to calculate the worksheet..

I remember using the API sometime ago, to achieve this cell highlighting effect which works on the screen layer without affecting the actual worksheet ... The code is a bit more complicated but works without any of the above mentioned issues.

I can't find it at the moment but if I do I'll post it here for the sake of enriching the subject.

 

[DanteAmor]

Let's wait for the OP test.
Good point, ScreenUpdating instead of Calculate. With that we solve the "problem" of calculation ", the problem of the protected sheet and the problem of the undo. A complicated code is not required, only the format conditions.

 

[Worf99]

Hola Dante,
thank you for your new input.
I like it, it's something I could find handy.

As regard to your previous code, it's needed even in Excel 2016 otherwise only the very first cell is highlighted when it's clicked and remains so even if you click another cell.

Thank you!
Matteo

 

[DanteAmor]

Ok, then use calculate or screenupdating.A pleasure to help you.