Modification of ThisWorksheet Macro

[jim may]

I have the following Macro that is working fine, EXCEPT if I select a cell that has PREVIOUSLY been color-formatted manually - the
below Macro NULLIFIES - meaning it resets my previously colored cell's format is set to "ColorIndex" of None. I need for the below
Macro (to be modified) so as to test if the currently selected cell "ALREADY HAS A COLORINDEX APPLIED", and if so, RESTORE this
Formatting AFTER MOVING AWAY from the cell. Can anyone help me with this modification, please...

Thanks in Advance..
Jim

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Static OldCell As Range
If bFlag = True Then
If Application.CutCopyMode = 0 Then
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
'OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
'OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
End If
End Sub

 

[Zot]

Not really understand what the whole thing started, but it looks like you trigger the macro on sheet selection change and not cell change in worksheet.

Since I don't know where bFlag condition comes from, I have to remark that out to test the code ?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Static OldColor As Long
Static OldCell As Range

On Error Resume Next
'If bFlag = True Then
    If Application.CutCopyMode = 0 Then
        If Not OldColor < 0 Then
            OldCell.Interior.ColorIndex = OldColor
        Else
            OldCell.Interior.ColorIndex = xlColorIndexNone
        End If
        Set OldCell = Target
        OldColor = Target.Interior.ColorIndex
        Target.Interior.ColorIndex = 6
    Else
        If Target Is Nothing Then
            Set OldCell = Target
        Else
            Set OldCell = Union(OldCell, Target)
        End If
    End If
'End If
On Error GoTo 0

End Sub

The reason I added On Error Resume Next was just because during initial run, the OldCell was not yet set (not defined) and will produce an error on line OldCell.Interior.ColorIndex = OldColor line. Otherwise, not problem

 

[jim may]

Thanks Zot

In my Workbook_Open Macro I have a line:
bFlag = True

Thanks to you I got it Working exactly like I wanted it...

Jim