Change cell fill color when unprotected cell selected -

[Stratfordoaks]

I'm struggling to find some code that changes the color (to light grey) of an unprotected cell in my sheet when its selected. When an unprotected cell is selected I want it to change color. The cell is then filled with a value and its color to return to original color when next unprotected cell selected.

[Sbarro79]

I don't know if this code meets your requirements, but maybe you find it a bit helpful:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Protect UserInterfaceOnly:=True
Cells.Interior.ColorIndex = xlNone
If ActiveCell.Locked = False Then ActiveCell.Interior.Color = RGB(221, 221, 221)
End Sub

[barryleajo]

With acknowledgement to Sbarro79
This variation will preserve the cell colors of existing protected cells
Note that it uses a helper cell ("XFD1") so this can't be used for other things

Also this code must be placed in the appropriate Sheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As String
ActiveSheet.Protect UserInterfaceOnly:=True
    If Target.Locked = False Then
        rng = Range("XFD1").Value
        Range(rng).Interior.ColorIndex = xlNone
        Target.Interior.ColorIndex = 15
        Range("XFD1").Value = Target.Address
    End If
End Sub

[Sbarro79]

Point for you barryleajo - I didn't think that there may be some protected cells already filled with color (but I am still a beginner in VBA and Excel).
Anyway here is my version of code that preserves colors in protected cells:

Private prevcell As Range

to be added above all procedures in Sheet module (global variable)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Protect UserInterfaceOnly:=True

If prevcell Is Nothing Then Set prevcell = Target
If Not prevcell Is Nothing And prevcell.Locked = False Then prevcell.Interior.ColorIndex = xlNone
If Target.Locked = False Then Target.Interior.Color = RGB(221, 221, 221)
Set prevcell = Target
End Sub

I also added that line:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveCell.Locked = False Then ActiveCell.Interior.ColorIndex = xlNone
End Sub

to avoid saving Workbook with unprotected cell filled with color

I don't know if the code is written properly, but it works for me

[Stratfordoaks]

Hi Sbarro79. Your first code worked great but I lost the fill colour in all my protected cells. Could you please explain where I put 'Private prevcell As Range' within the code you have given. Much appreciated.

[Sbarro79]

Hi Stratfordoaks, sorry for late reply - work
For the code line you asked: just open your workbook, open VBA editor (ALT+F11), in the top left panel select your worksheet (double click) and the code should be put in the first or second line (if the first line is Option Explicit)

I also changed the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveCell.Locked = False Then ActiveCell.Interior.ColorIndex = xlNone
End Sub

to that code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("x").Activate
If ActiveCell.Locked = False Then ActiveCell.Interior.ColorIndex = xlNone
End Sub

because I experienced that previous code didn't work when I clicked save button before closing workbook. And now, I hope, it will be alright.
Please remember to change "x" to worksheet name you work on.

I hope I explained everything clearly enough.

[Stratfordoaks]

Hi Sbarro79 - I did get it working yesterday but I have other macros that run from this sheet before it saves and closes. I think the code was protecting cells and stopped these macros working. Its very frustrating because the grey fill colur in selected cell made it far easier for the operator to see which cell they were in. I need your code to not work when my oter ones run. I dont know if thats possible? Ps could you put all your code together for me so that I am sure I have it all in order. Im not quite sure which bits you want me to use. Many thanks for your time and patience.

[Sbarro79]

Sorry to hear that there is a problem with my code. I'm just guessing that your worksheet was already protected when you open the workbook and this didn't allow your macros to be executed. But as I wrote, I'm still a beginner in VBA and I need to learn more about it, and learn, and learn...
In the attached file you will find code (slightly updated, if we can say like that) plus some silly simple macro (commandbutton_click and trial procedure) that works when sheet is protected (you don't need to add it to your worksheet). Please check, if the code works now as you wanted.

[Stratfordoaks]

Thanks Sbarro79. I will have a dabble in the morning and let you know how it goes.