Unlock based on cell colour

  • Thread starter Thread starter violasrbest
  • Start date Start date
V

violasrbest

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
 
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
 
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.
 
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
 
Thanks Rick, works like lightning

Rick Rothstein said:
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)




.
Click to expand...
 
Back
Top