Unprotect workbook, find certain RGB cells even if rows are deleted

[jarzack]

I'm currently working on an Excel form, where one group will unprotect the workbook to delete certain rows, and enter in data. Then they will protect the workbook and Save.

The second group will only enter data in cells that are only colored RGB (253, 233, 217). All of these RGB cells are locked.

This workbook will contain BeforeClose and BeforeSave events. I need help with the VBA code finding any of these colored cells regardless if the original cell location has been removed, then lock these RGB cells and protect workbook.

Any help on this would be appreciated.

Here is the VBA so far.


Sub A_SelectRange()
Range("B2:M276").Select
End Sub
Sub B_SelectOrangeCells()
Dim rCell As Range
Dim lColor As Long
Dim rColored As Range

'Select the orange colored cells
lColor = RGB(253, 233, 217)


Set rColored = Nothing
For Each rCell In Selection
If rCell.Interior.Color = lColor Then
If rColored Is Nothing Then
Set rColored = rCell
Else
Set rColored = Union(rColored, rCell)
End If
End If
Next
If rColored Is Nothing Then
MsgBox "No cells match the color"
Else
rColored.Select

End If
Set rCell = Nothing
Set rColored = Nothing
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim rCell As Range, lColor As Long, rColored As Range
lColor = RGB(253, 233, 217)
Set rColored = Nothing
For Each rCell In Range("B2:M276")
If rCell.Interior.Color = lColor Then
If rColored Is Nothing Then
Set rColored = rCell
Else
Set rColored = Union(rColored, rCell)
End If
End If
Next
If rColored Is Nothing Then
MsgBox "No cells match the color"
Else
rColored.Select
Application.ScreenUpdating = True
MsgBox "All orange cells must contain data in order to save this document."
End If
Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Dim rCell As Range, lColor As Long, rColored As Range
lColor = RGB(253, 233, 217)
Set rColored = Nothing
For Each rCell In Range("B2:M276")
If rCell.Interior.Color = lColor Then
If rColored Is Nothing Then
Set rColored = rCell
Else
Set rColored = Union(rColored, rCell)
End If
End If
Next
If rColored Is Nothing Then
MsgBox "No cells match the color"
Else
rColored.Select
Application.ScreenUpdating = True
MsgBox "All orange cells must contain data in order to save this document."
End If
SaveAUI = False
Cancel = True
End Sub
Sub RunMacros()
Call SelectRange
Call SelectOrangeCells
End Sub

 

[iliace]

If user group one deletes a row, those cells are gone, so you won't be able to act on them, VBA or otherwise. You can't protect non-existent cells.

Now, let's say you have colored cells A4, B4, C4. Then, someone deletes row 3:3. Those three cells then become A3, B3, C3 (shift up one row). Their properties - number format, fill color, protection settings, etc - will not change. They simply moved; they now have a new address.

So, if they were already protected and you want to keep it that way, no further action should be necessary. If I am reading correctly, user group two only inputs data in cells that are already colored - not coloring new ones?

Or, is user group one coloring new cells to be updated by user group two, and those need to be protected?