Reset previous active cell

ArtySin · Feb 16th 2018

I have some code below which changes the background colour and capitalises the active cell. When I click on another cell the previous cell is still capitalised. Could someone please modify the code so that the previous cell isn't capitals nor bold anymore.
Many thanks

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbCyan
Target.Formula = UCase$(Target.Formula)
Target.Font.bold = True
Application.ScreenUpdating = True
End Sub

nilem · Feb 16th 2018

Try this (in sheet module)

Code

Option Explicit
Dim r As Range


Private Sub Worksheet_Activate()
Set r = ActiveCell
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub


With r
    .Interior.ColorIndex = xlNone
    .Formula = LCase$(.Formula)
    .Font.Bold = False
End With


With Target
    .Interior.Color = vbCyan
    .Formula = UCase$(.Formula)
    .Font.Bold = True
End With


Set r = Target
End Sub

Display More

ArtySin · Feb 16th 2018

Thanks VERY much Nilem, it works a treat

Reset previous active cell

Participate now!

Share