Unlock certain cells based on ColorIndex

S

Samantha

I am working on a document that calculates indices based on a set of default
weightings. I want to allow the user to redistribute these weights without
changing the default information. It is set up so that the user can make
alterations in the cells that have a yellow background. There are many sheets
in the workbook and the location of the yellow cells are not uniform. I've
written the following code that loops through each worksheet, unlocks the
yellow cells, and protects the sheet. The trouble is that it is very buggy
and for some reason selects some yellow cells but not others. Occasionally I
get an error 1004 message. Is there a more elegant way to do this?


For Each wks In ActiveWorkbook.Worksheets
str = wks.Name

'Unlock yellow cells
For ColIndex = 1 To 40
For RowIndex = 1 To 320
If Cells(RowIndex, ColIndex).Interior.ColorIndex = 36 Then
Cells(RowIndex, ColIndex).Locked = False
Cells(RowIndex, ColIndex).FormulaHidden = False
End If

Next RowIndex
Next ColIndex

'Protect worksheet
wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Next wks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top