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

Worf99 · Aug 13, 2019

Worf99 said:
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.

Jaafar, Dante's code works for me now but I'd love to see if yours works too even with sheet protected!

DanteAmor · Aug 13, 2019

Worf99 said:
Dante, I did it!

I realized I had to translate your formula in Italian: =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)

Now it works like a charm!

Muchas gracias de Italia!
Muchas gracias de Italia!
Muchas gracias de Italia!
Muchas gracias de Italia!

I'm glad to help you. Thanks for the feedback.

Jaafar Tribak · Aug 13, 2019

Worf99 said:
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.

You could unprotect the worksheet at the start of the code and protect it back at the end or you could protect the interface only

Worf99 · Aug 14, 2019

Jaafar Tribak said:
You could unprotect the worksheet at the start of the code and protect it back at the end or you could protect the interface only

Ok, I'll try when I get back from work.
Thanks again!

Worf99 · Aug 14, 2019

Jaafar Tribak said:
You could unprotect the worksheet at the start of the code and protect it back at the end or you could protect the interface only

Can you tell me please how to protect the interface only in VBA?

Jaafar Tribak · Aug 14, 2019

Worf99 said:
Can you tell me please how to protect the interface only in VBA?

Remove the existing worksheet protection and paste the code below in the worksheet module:
(Change password in red as required.)

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Me.ProtectContents Then
        Me.Protect Password:="[COLOR=#ff0000][B]1234[/B][/COLOR]", UserInterfaceOnly:=True, AllowFormattingCells:=True
    End If
    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

Note:
Once you get the above code working, can you save the workbbook & close it then reopen it to see if the protection persists after closing the workbook ? In excel 2016, the UserInterfaceOnly protection persists after having closed the workbook but I am not sure about previous excel versions.

Worf99 · Aug 14, 2019

Jaafar, now it works!
I have Excel 2016 and protection persists after closing the file.
I now have 2 methods (yours and Dante's) to achieve what my goal was.
It seems to me they're both efficient.
Thank you so much!!!

If/when you have time, to help me understanding VBA, could you please comment each row of your code?

Thanks again!!!!
Matt

Jaafar Tribak · Aug 14, 2019

Worf99 said:
Jaafar, now it works!

If/when you have time, to help me understanding VBA, could you please comment each row of your code?

Hers is the logic behind the code:

First, the code checks if the worksheet is un-protected. If so it protects the interface only so that the user cannot edit the sheet but the vba code can.

After that, the code looks at the Tag Property of the &File commandbar control (could use any other control) to see if there is in it the address of the previous selection and if so it deletes the conditional formatting associated with the previous selection.

Then, the code simply sets the conditional formatting for the current selection to TRUE and stores the selection address in the commandbar control Tag Property for later use.

I could have stored the previous selection address in a module level variable or static variable but I didn't do so in case a vbaproject loss of state happens and the variables are accidently released.

Note:
DanteAmor's code works but requires forcing a calculation each time a sheet selection is made which can have a bad impact on performance if there are lots of volatile functions in the worksheet(s)... Also, his code will only highlight the first cell in the selection not the entire selection.

BTW, In order to avoid repeated calculations in DanteAmor's code, you could replace this :

Code:

[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]
[COLOR=#333333]    Calculate[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

With this :

Code:

[COLOR=#333333]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/COLOR]
    Application.ScreenUpdating = True
[COLOR=#333333]End Sub[/COLOR]

Worf99 · Aug 15, 2019

Jaafar,
I'm really grateful. Not only you're a code master but you are so kind and helpful to explain your code in a clear way to a beginner like me.
Thank you so much.
All the best!
Ciao from Italy.
Matteo

DanteAmor · Aug 15, 2019

Hi @Woor99:

The title of the question is active cell or selection, well, my answer focused on the activecell.
When you select a range of cells, excel hightlight the cells.
I propose an option to illuminate the entire row and the entire column at the same time. Something like this:

Add 2 conditional formats

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:

=COLUMN()=CELL("column")

5. Click Format.
6. In the Color box, select your favorite color.
7. Click OK until the dialog boxes are closed.

Repeat steps 1 to 4 and now put this formula
=FILA()=CELDA("fila")
Finish steps 5 to 7.

Remember to translate the formula into Italian!

Ready, select any cell.

-------------------------------------------------------------

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.

-------------------------------------------------------------

By the way, an advantage of using my procedure if you edit a cell you can press Ctrl-Z and excel undo the change. While with the Jaafar option there is no going back.

Good luck!

Highlight active cell or selection but restore previous fill color

Worf99

New Member

Worf99

New Member

Excel Facts

DanteAmor

Well-known Member

Jaafar Tribak

Well-known Member

Worf99

New Member

Worf99

New Member

Jaafar Tribak

Well-known Member

Worf99

New Member

Jaafar Tribak

Well-known Member

Worf99

New Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

Highlight active cell or selection but restore previous fill color

New Member

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known 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