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 Sheet In ActiveWorkbook.Worksheets
For Each Item In Sheet.UsedRange.Cells
'Item.Value = Item.Interior.ColorIndex
If Item.Locked = False Then
Item.AddComment (CStr(Item.Interior.ColorIndex))
Item.Interior.ColorIndex = 37
End If
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
If Item.Locked = False Then
Item.Interior.ColorIndex = CLng(Item.Comment.Text)
Item.Comment.Delete
End If
Next Item
Next Sheet
End Sub



"S30 via OfficeKB.com" wrote:

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

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

JMB wrote:
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 Sheet In ActiveWorkbook.Worksheets
For Each Item In Sheet.UsedRange.Cells
'Item.Value = Item.Interior.ColorIndex
If Item.Locked = False Then
Item.AddComment (CStr(Item.Interior.ColorIndex))
Item.Interior.ColorIndex = 37
End If
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
If Item.Locked = False Then
Item.Interior.ColorIndex = CLng(Item.Comment.Text)
Item.Comment.Delete
End If
Next Item
Next Sheet
End Sub

Hi All,

[quoted text clipped - 44 lines]
Application.ScreenUpdating = True
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200604/1