Highlight Column When Cell Selected Without Removing the Sheets Other Background Colours

Try this out:

Private colno As Long
Private colcolour As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    If colno > 0 And colcolour <> 0 Then
        Cells(1, colno).EntireColumn.Interior.ColorIndex = colcolour
    End If
    colno = Target.Column
    colcolour = Target.Interior.ColorIndex
With Target
        ' Highlight the entire row and column that contain the active cell
       
        .EntireColumn.Interior.ColorIndex = 43
    End With
    Application.ScreenUpdating = True
End Sub

You did copy the 2 lines above the sub also right? those are private variables to the sheet, that store the previous column and colour

? It should do that the way it is... I made a sheet yellow, and each time I selected another, the green went back to yellow, and the new one green.

I even tried making some yellow, others red, others white, and some no colour... the active column goes to green, and the others revert to original colour... can you maybe attach your workbook? No data needed for this... just the book with at least the sheet that has this code.

mackenzie,

Now I see the problem... you have different colours in different rows... i had not realized that, so my code based the "old" colour stored in a variable on the cell that gets clicked. I'll have to give this some thought.... but I am not sure what you want is possible without storing a LOT of temporary data.

Do you know how many rows will need to have this applied? What I mean is, is there a limited number of rows that will need to be reset to the "old" colour? Or will the number of rows grow over time?

Ok, give this one a shot... I have also provided a workbook_beforesave macro, so that on saving it does not save with the column highlighted (I believe... leaving final testing to you). The only thing is.... to avoid saving with highlight, the variables that store the old column and colour need to be public variables, and you are not allowed to have public variables in events. So in a normal module, at the top, put:

Public colno As Long
Public colcolour(1 To 100)

Then in the sheet's code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    If colno > 0 And colcolour(1) <> 0 Then
        For i = 1 To 100
            If colcolour(i) <> xlNone Then Cells(i, colno).Interior.Color = colcolour(i)
            If colcolour(i) = xlNone Then Cells(i, colno).Interior.ColorIndex = xlNone
        Next i
    End If
    colno = Target.Column
    For i = 1 To 100
        If Cells(i, Target.Column).Interior.ColorIndex <> xlNone Then colcolour(i) = Cells(i, Target.Column).Interior.Color
        If Cells(i, Target.Column).Interior.ColorIndex = xlNone Then colcolour(i) = xlNone
    Next i
With Target
        ' Highlight the entire row and column that contain the active cell
       
        .EntireColumn.Interior.ColorIndex = 43
    End With
    Application.ScreenUpdating = True
End Sub

And then in "ThisWorkbook" :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False
If colno > 0 And colcolour(1) <> 0 Then
    For i = 1 To 100
        If colcolour(i) <> xlNone Then Cells(i, colno).Interior.Color = colcolour(i)
        If colcolour(i) = xlNone Then Cells(i, colno).Interior.ColorIndex = xlNone
    Next i
End If
Application.ScreenUpdating = True
End Sub