Color issue confusing me

[Scot]

I pasted this useful code (see below) into my Excel 97-2003 worksheet to highlight the active cell. All is good, works great BUT, I lost all the other colors in the worksheet :(
Now l knew it would do this but my question is, can the code be "modified" in order to keep the coloring l had on my spreadsheet AND highlight the active cell? (I am using Excel 97-2003 and the Color Index option, don't think my version supports the RGB color method).


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNoneTarget.Interior.ColorIndex = 33
End Sub

Any constructive help would be very much appreciated.

[JKwan]

well, that line makes the entire sheet without any color, just delete that line

Cells.Interior.ColorIndex = xlNone

[Scot]

well, that line makes the entire sheet without any color, just delete that line

Cells.Interior.ColorIndex = xlNone


I already tried that. All it does is colors every single cell l select, but they stay that way instead of going away when you select another cell. :crying:

[SamT]

All the code above is working correctly.

What are you wanting to happen. (From start to finish.)

[SamT]

Just guessing but this will highlight the selection and restore it when you select a new cell.

Paste the Code into the modules indicated, then close the workbook and save it. It sould work ehen you opne the book again.

IF you modify the cose, besure to cose the book and reopen it before trying the new code.

Standard Module Code
Option Explicit

Dim PreviousCell As Range
Dim PreviousColor As Long
Dim ThisCell As Range


ThisWorkbook Code
Option Explicit

Private Sub Workbook_Activate()
Application.EnableEvents = False
Set ThisCell = ActiveSheet.Cells(1, 1)
ThisCell.Select
Set PreviousCell = ThisCell
PreviousColor = ThisCell.Interior.ColorIndex
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not PreviousCell Is Nothing Then _
PreviousCell.Interior.ColorIndex = PreviousColor
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not PreviousCell Is Nothing Then _
PreviousCell.Interior.ColorIndex = PreviousColor
Set PreviousCell = Nothing
Set ThisCell = Nothing
PreviousColor = xlNone
End Sub

Private Sub Workbook_Deactivate()
If Not PreviousCell Is Nothing Then _
PreviousCell.Interior.ColorIndex = PreviousColor
Set PreviousCell = Nothing
Set ThisCell = Nothing
PreviousColor = xlNone
End Sub

Worksheet Code
Option Explicit

Private Sub Worksheet_Activate()
Application.EnableEvents = False
Set ThisCell = Range("A1")
ThisCell.Select
Set PreviousCell = ThisCell
PreviousColor = ThisCell.Interior.ColorIndex
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Deactivate()
If Not PreviousCell Is Nothing Then _
PreviousCell.Interior.ColorIndex = PreviousColor
Set PreviousCell = Nothing
Set ThisCell = Nothing
PreviousColor = xlNone
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not PreviousCell Is Nothing Then _
PreviousCell.Interior.ColorIndex = PreviousColor
Set ThisCell = Target
Set PreviousCell = ThisCell
PreviousColor = ThisCell.Interior.ColorIndex
ThisCell.Interior.ColorIndex = 33
End Sub

[Scot]

All the code above is working correctly.

What are you wanting to happen. (From start to finish.)

Was moving house and have no internet access right now, so apologize for late reply....... Thanks for the help BTW

What you said in your second reply was correct, I wanted the selected cell highlighted and that would go away and highlight another cell when I selected it. Because l am a complete noob as far as VBA goes, just answer me this if you would, can I paste those codes (separately of course) into the VBA window after accessing it by right clicking the worksheet rider at the bottam and hitting "view code"?, or do I have to open something else to drop the different codes into?

[SamT]

Yes, but follow the directions carefully.

[Scot]

Yes, but follow the directions carefully.

​Thanks