Color the Font of Cells

My customer had a number of workbooks, each having up to 20 worksheets in each one. He was trying to unlock some cells and lock down the headings and formula 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.

Here is the code for changing the font color of the cells. 
Option Explicit

Public IteM As Variant, SheetR As Integer, DdD As Integer, SheetC as Integer

Sub ColorActive()
    SheetR = 0					'Reset counter
    SheetC = ActiveWorkbook.Worksheets.Count	'See how many sheets to work on
    For DdD = 1 To SheetC			'Work from Sheet 1 to ...
        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 Then		'If not locked
                IteM.Font.ColorIndex = 32       'Blue for unlocked
                SheetR = SheetR + 1		'Add to cells fixed count
            ElseIf IteM.Locked = True Then	'If IS locked ...
                IteM.Font.ColorIndex = xlAutomatic	'Set color to default
            End If
        Next				'Loop next cell
        ActiveSheet.Protect		'Re-protect
    Next
  MsgBox SheetR & " cells were colored Blue if 'unlocked'", 64	'Done message
End Sub

How do I use this code? Copy and paste into your module sheet.

When I delivered to my customer, the opening page of the workbook contained this message:

There is a macro behind this sheet. Be sure that the workbook that you want to color the un-protected cells is in front and selected.

Click Tools | Macro | Macros | ColorActive

It grinds through all the worksheets in the active workbook and makes the “unlocked” cells text colored blue and the “locked” cells, black (default).

At the end, it tells you how many cells were affected.

Alan's Home Falkland Islands stamps Excel & VBA

Updated: 17 Mar 2001 11:09:04