Finding unlocked cells in Excel

Hi All,

I have a macro which find and adds color to all unlocked/unprotected cells in
Excel. Unfortunately once I remove the color from unlocked cells the macro
also removes original colors applied to the cells prior to running these
macros.

Any ideas of how I could improve these macros?

Thanks a lot in advance

Steve

Sub Color_Unprotected_cells()
Application.ScreenUpdating = False
Set A = ActiveWorkbook.ActiveSheet
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Activate
Sheet.Cells.Select
For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)

'Item.Value = Item.Interior.ColorIndex
If Item.Locked = False Then
Item.Interior.ColorIndex = 37
End If
Next
ActiveSheet.Range("A1").Select
Next Sheet
A.Activate
Application.ScreenUpdating = True
End Sub

Sub Remove_Color_Unprotected_cells()
Application.ScreenUpdating = False
Set A = ActiveWorkbook.ActiveSheet
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Activate
ActiveSheet.Cells.Select
For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)
If Item.Locked = False Then
Item.Interior.ColorIndex = xlNone
End If
Next
ActiveSheet.Range("A1").Select
Next Sheet
A.Activate
Application.ScreenUpdating = True
End Sub

--
Message posted via http://www.officekb.com

So you color the unprotected cells, then you want to be able to change the
color back to what it was before?

The easiest way I could think of is if your spreadsheets are not currently
using any comments, you could save the existing color index number in a
comment box before changing the color, then get the value from the comment
box when you change it back. Other than that, you could have to create a
separate worksheet that had a list of cells (worksheet name and cell address)
and the existing color index number. Then when changing the cell color back,
lookup the old value in your table, change the cell color back, and delete
the entry from the table.

Saving the value as a comment would look like:

Sub Color_Unprotected_cells()
Dim Sheet As Worksheet
Dim Item As Range

For Each Item In Sheet.UsedRange.Cells

Item.AddComment (CStr(Item.Interior.ColorIndex))

Next Item
Next Sheet
End Sub

Sub Remove_Color_Unprotected_cells()
Dim Sheet As Worksheet
Dim Item As Range

For Each Sheet In Worksheets
For Each Item In Sheet.UsedRange.Cells

Item.Interior.ColorIndex = CLng(Item.Comment.Text)
Item.Comment.Delete
End If
Next Item
Next Sheet
End Sub

Hi JMB,

this is an excellent suggestion, however, unfortunately I am using comments.
It becomes messy once you start adding to exsisting comments. I have tried
writing the colorindex into a spreadsheet, which works fine. Unfortunately
this type of macro runs forever.
Any other ideas how I could make this work?

Thanks you very much

Regards

Steve

>> Hi All,
>>
>[quoted text clipped - 44 lines]

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200604/1

Instead of modifying the color you could try making the cells visible by
setting the diagonal borders (unless you're already using those....)

Tim

'***************************************************

For Each Sheet In ActiveWorkbook.Worksheets
FlagCells Sheet.UsedRange, True 'mark
'FlagCells Sheet.UsedRange, False 'unmark
Next Sheet
End Sub

Sub FlagCells(ProcessRange As Range, ShowUnLocked As Boolean)
Dim c As Range
Dim t
t = IIf(ShowUnLocked, xlContinuous, xlNone)
For Each c In ProcessRange
If Not c.Locked Then
With c.Borders(xlDiagonalDown)
.Weight = xlThin
.ColorIndex = xlAutomatic
.LineStyle = t
End With
With c.Borders(xlDiagonalUp)
.Weight = xlThin
.ColorIndex = xlAutomatic
.LineStyle = t
End With
End If
Next c

End Sub
'*******************************************************

"S30 via OfficeKB.com" <u20199@uwe> wrote in message
news:5e5b843e0adf6@uwe...

Tim this is excellent. Thank you so much!

Steve

>> Hi JMB,
>>
>[quoted text clipped - 61 lines]