View Full Version : VBA to block black filled cells as sheet updated

07-30-2015, 06:38 AM
Hello all,

Looking for some possible input on how to write a code that protects only black cells. I am new to VBA so my experience is very little, however I do have some experience with Matlab.

Background on Spreadsheet:

The spreadsheet I have made essentially follows a flow chart, when you open the spreadsheet only cell in range not formatted black is A1, when you input A1, B1 becomes unformatted (conditional formatting) and so on flowing horizontally, however depending on input may skip portions. I do have the sheet protected to leave titles and other info protected already.

From my limited VBA it seems like there may be a way to:

Protect Sheet(titles ect.) + Black Filled Cells
Input in not formatted cell(which un formats new cell, already does this)
Update Protection on Black filled cells

Any help is much appreciated for this newbie!

07-30-2015, 07:12 AM
In the sheet's code-module, perhaps:
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect userinterfaceonly:=True
If Target.Cells.Count = 1 Then
If Target.Offset(, 1).DisplayFormat.Interior.Color <> vbBlack Then Target.Offset(, 1).Locked = False
End If
End Sub
No re-locking of black cells at the moment.

07-31-2015, 03:12 PM
cross posted http://www.excelforum.com/excel-programming-vba-macros/1096554-vba-to-continuously-block-shaded-black-cells.html