Unlock based on cell colour
 

Hi

I have a spreadsheet with input cells coloured blue. I need a macro to
unlock all blue cells. I know I need to use Colorindex but I can't work out
how to put it all together.

Many thanks

Unlock based on cell colour
 

Something like this.

With Sheets("Sheet1")
..Unprotect
For Each c In .Range("A2:F100")
If c.ColorIndex = 5 Then
c.Locked = False
End If
Next
..Protect
End With

"violasrbest" wrote in message
...
Hi

I have a spreadsheet with input cells coloured blue. I need a macro to
unlock all blue cells. I know I need to use Colorindex but I can't work
out
how to put it all together.

Many thanks

Unlock based on cell colour
 

Here is a non-looping method that should be very fast...

Sub FindRedFont()
Application.FindFormat.Interior.ColorIndex = 5
Application.FindFormat.Locked = True
Application.ReplaceFormat.Locked = False
Worksheets("Sheet1").Cells.Replace "", "", _
SearchFormat:=True, ReplaceFormat:=True
End Sub

Just change the ColorIndex value of 5 which I used in the first statement
above to the actual ColorIndex of the cells you want to unlock and change
the Worksheet name from "Sheet1" which I used in the last (continued) line
above to the actual name for the Worksheet with your "blue" cells on them.

--
Rick (MVP - Excel)



"violasrbest" wrote in message
...
Hi

I have a spreadsheet with input cells coloured blue. I need a macro to
unlock all blue cells. I know I need to use Colorindex but I can't work
out
how to put it all together.

Many thanks

Unlock based on cell colour
 

Ignore the macro name I used... I cannibalized another routine I had laying
around to make the macro I posted for you and forgot to change the original
macro's name. Here is the same code but with a more apt macro name (which
you can change to suit your preferences, of course)...

Sub UnlockBlueCells()
Application.FindFormat.Interior.ColorIndex = 5
Application.FindFormat.Locked = True
Application.ReplaceFormat.Locked = False
Worksheets("Sheet1").Cells.Replace "", "", _
SearchFormat:=True, ReplaceFormat:=True
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Here is a non-looping method that should be very fast...

Sub FindRedFont()
Application.FindFormat.Interior.ColorIndex = 5
Application.FindFormat.Locked = True
Application.ReplaceFormat.Locked = False
Worksheets("Sheet1").Cells.Replace "", "", _
SearchFormat:=True, ReplaceFormat:=True
End Sub

Just change the ColorIndex value of 5 which I used in the first statement
above to the actual ColorIndex of the cells you want to unlock and change
the Worksheet name from "Sheet1" which I used in the last (continued) line
above to the actual name for the Worksheet with your "blue" cells on them.

--
Rick (MVP - Excel)



"violasrbest" wrote in message
...
Hi

I have a spreadsheet with input cells coloured blue. I need a macro to
unlock all blue cells. I know I need to use Colorindex but I can't work
out
how to put it all together.

Many thanks

Unlock based on cell colour
 

Thanks Rick, works like lightning

"Rick Rothstein" wrote:

Ignore the macro name I used... I cannibalized another routine I had laying
around to make the macro I posted for you and forgot to change the original
macro's name. Here is the same code but with a more apt macro name (which
you can change to suit your preferences, of course)...

Sub UnlockBlueCells()
Application.FindFormat.Interior.ColorIndex = 5
Application.FindFormat.Locked = True
Application.ReplaceFormat.Locked = False
Worksheets("Sheet1").Cells.Replace "", "", _
SearchFormat:=True, ReplaceFormat:=True
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Here is a non-looping method that should be very fast...

Sub FindRedFont()
Application.FindFormat.Interior.ColorIndex = 5
Application.FindFormat.Locked = True
Application.ReplaceFormat.Locked = False
Worksheets("Sheet1").Cells.Replace "", "", _
SearchFormat:=True, ReplaceFormat:=True
End Sub

Just change the ColorIndex value of 5 which I used in the first statement
above to the actual ColorIndex of the cells you want to unlock and change
the Worksheet name from "Sheet1" which I used in the last (continued) line
above to the actual name for the Worksheet with your "blue" cells on them.

--
Rick (MVP - Excel)



"violasrbest" wrote in message
...
Hi

I have a spreadsheet with input cells coloured blue. I need a macro to
unlock all blue cells. I know I need to use Colorindex but I can't work
out
how to put it all together.

Many thanks

.