Excel VBA

Color Unlocked Cells

The good thing about this macro is that it can be run multiple times against the same sheets so if you are constantly changing and checking your work, you get immediate gratification.

My new customer had a more complicated worksheet that I need to have more criterias. 2024 update is essentially a complete re-write.

Option Explicit

Sub ColorActive()
   Dim IteM As Variant
   Dim SheetR As Integer
   Dim DdD As String
   Dim F    As Long
   Dim O    As Long
   Dim L    As Long
   Dim SheetC As Integer
    SheetR = 0                  'Reset counter
    SheetC = ActiveWorkbook.Worksheets.Count    See how many sheets to work on
    DdD = ActiveSheet.Name
        Worksheets(DdD).Activate        'Activate active sheet
   ' ActiveSheet.Unprotect            'Unprotect - assuming no password
        For Each IteM In ActiveSheet.UsedRange  'Color cells according to locked
            If IteM.Locked = False And IteM.Formula = True Then 'Contains formula
                IteM.Interior.ColorIndex = xlAutomatic
                F = F + 1
            ElseIf IteM.Locked = False Then   ' If not locked
                IteM.Interior.Color = &HAF1E9A9        'Blue for unlocked 
                SheetR = SheetR + 1     'Add to cells fixed count
                O = O + 1
            ElseIf IteM.Locked = True Then  'If IS locked ...
                IteM.Interior.ColorIndex = xlAutomatic 'Set color to default
                L = L + 1
            End If
        Next                'Loop next cell

  MsgBox SheetR & F & " Formulas were skipped," & O & " cells were colored, and " & L _
	& " locked cells were skipped on " & DdD, 64 'Done message
End Sub