Solved: Protect based on cell colour

[khalid79m]

I need to vba script to check

range A3 to IV & LASTROW

and any cells in this range which have

an .Interior.ColorIndex = 4

to lock and protect with the password "Sayyaf"

any help people very desperate to get this to work. The reason is the cells in blue are data which should not be amended or changed

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 3 To LastRow

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = 1 To lastCol

.Cells.Locked = .Cells(i, j).Interior.ColorIndex = 4
Next j
Next i

.Protect Password:= "Sayyaf"
End With

End Sub

[khalid79m]

Hi I dont follow your code, maybe too complex for me? could you explain what each bit does especially the test column..

also when i run this it locks all the cells. I only want it to lock cells with a background colour 4. Just to let you know the cells with a background colour 4 and blank cells if that helps.

[Ken Puls]

So... can I ask a dumb question?

Why not just use styles in the workbook? You can set protection and colours there, and then update them all in one go if your needs change. No VBA required...

[khalid79m]

styles ? never used that before any tutorials ?

[Ken Puls]

Honestly, not that I've seen. What version of Excel are you using? I'll see if I can write something up for you.

[Ken Puls]

Hi there,

I actually had started to write something up ages ago, and threw some time at it tonight. You can have a look here (http://www.excelguru.ca/node/53).

The examples actually attack this from the other angle, (unlocking the cells for data entry,) but I'm sure you can turn it the other way pretty easily.

Let me know what you think. I kind of rushed through it, so if it needs some polishing, feel free to shoot me some suggestions. :)

HTH,