Code to colour Unlocked cells

[Stuart]

I create a workbook with just one sheet, and make sure that
range("A1") is not Locked. With this book as the activeworkbook,
I run this code:

Sub Test_Protection()
Dim ws As Worksheet, c As Range
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
For Each c In .UsedRange.Columns("A:G")
With c
If Not c.Locked = True Then
'User is permitted to edit this cell, so color it:
c.Interior.ColorIndex = 34
End If
If Not .Range("A1").Locked = True Then
.Range("A1").Interior.ColorIndex = 34
End If
End With
Next
End With
Next
End Sub

In this example, the 'c' code does not recognise that "A1" is
not Locked, but the "A1" code does, and the cell is coloured
correctly.

Why is the line 'If Not c.Locked = True Then' failing please?

Regards.

 

[Bob Phillips]

Stuart,

You are processing the columns, not the cells. Try this

Sub Test_Protection()
Dim ws As Worksheet, c As Range
Dim cell As Range
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
For Each c In .UsedRange.Columns("A:G")
For Each cell In c.Cells
With cell
If Not cell.Locked = True Then
'User is permitted to edit this cell, so color it:
cell.Interior.ColorIndex = 34
End If
End With
Next cell
Next c
End With
Next
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[kkknie]

It appears that when you specify the columns in the .UsedRange property
it returns the entire used range in the column to your c variable.
Then the checking for locked will not work (since the entire range i
not locked). You will need to specify your range in a different manor
Something like:

Dim BigRange as Range
Set BigRange = Range("A1:G" & Range("G65536").End(xlUp).Row

 

[Dana DeLouis]

Don't know if this would help, but in later versions of Excel, the following
appears to work... without a loop.

Sub Demo()
Application.FindFormat.Locked = False
Application.ReplaceFormat.Interior.ColorIndex = 4
Columns("A:G").Replace _
What:=vbNullString, _
Replacement:=vbNullString, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=True, _
ReplaceFormat:=True

End Sub

HTH :>)
Dana DeLouis

I create a workbook with just one sheet, and make sure that
range("A1") is not Locked. With this book as the activeworkbook,
I run this code:

Sub Test_Protection()
Dim ws As Worksheet, c As Range
For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
For Each c In .UsedRange.Columns("A:G")
With c
If Not c.Locked = True Then
'User is permitted to edit this cell, so color it:
c.Interior.ColorIndex = 34
End If
If Not .Range("A1").Locked = True Then
.Range("A1").Interior.ColorIndex = 34
End If
End With
Next
End With
Next
End Sub

In this example, the 'c' code does not recognise that "A1" is
not Locked, but the "A1" code does, and the cell is coloured
correctly.

Why is the line 'If Not c.Locked = True Then' failing please?

Regards.

 

[Stuart]

Many thanks to you both. I see what you mean.
One further question please:

When individual cells are coloured it's not so bad that Excel's
default gridlines are obliterated.........but when a large portion
of the screen is coloured, it's difficult to orientate without
those gridlines.

How do I get the gridlines back for the cells that change colour
.....or, would it be quicker to set the gridlines for the usedrange
once the colour-changing is complete?

Regards.

 

[Tom Ogilvy]

You have to use borders.

--
Regards,
Tom Ogilvy

Many thanks to you both. I see what you mean.
One further question please:

When individual cells are coloured it's not so bad that Excel's
default gridlines are obliterated.........but when a large portion
of the screen is coloured, it's difficult to orientate without
those gridlines.

How do I get the gridlines back for the cells that change colour
....or, would it be quicker to set the gridlines for the usedrange
once the colour-changing is complete?

Regards.

 

[Stuart]

Many thanks but it would appear not for me!
Using Excel 2000 and Win2k.

I get this error on the line:
Application.FindFormat.Locked = False

Run time error 438
Object doesn't support this property or method

I had begun to wonder if I could use the statement
.EnableSelection = xlUnlockedCells
in some way, since this seems to indicate a way to
'globally' identify the unlocked cells, rather than a
cell by cell approach.

Regards.

 

[Stuart]

Thanks and Regards.

You have to use borders.