Disallowing user to change cell color

[vikosh]

Hello dear excel-lers,

I have a workbook that helps me to record people's phone attendance on weekly basis.
Each sheet is a list with names and timeslots. Users then are expected to choose from a drop-down menu 1 of 3 options (1-office, 2-home office, 3-formation).
There is also a formula that calculated a sum of slots that are "covered" by the employees: for example from 8 to 8.30 there are two users that chose 1 and two that chose 2 (option 3 is not calculated). So the total will be 4 people that cover 8-8.30 slot.
Once someone chooses an option from the dropdown list, the cell changes color according to the selection - conditional formatting again.
There also a rule that if the sum is, i.e., more or equal than 4, than the cell with the slot timing turns green color.

Anyways, eventually the sheet look very structured and colorful.

So, sometime users instead of choosing from the dropdown list they just change the cell color. In that case, obviously, the cell contains no value (office or home office) and therefore cannot be calculated by the formula and in turn slot cell color wont change.

Is there a way, probably VBA, to stop users from changing certain cell range color change, but still be able to choose the drop down items?

Did i already confuse everybody?

Thank you in advance,

 

[Jaafar Tribak]

Use sheet protection.
Before protecting the worksheet, unlock the cell housing the dropdown (along other editable cells)
Edit: This will obviously prevent the user from formatting all cells, not just the one you want.

 

[Logit]

Paste the following into the WorkSheet module :

Option Explicit

Private prevCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Call CheckAndChangeColor(Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if there was a previously active cell and it's no longer the active cell
    If Not prevCell Is Nothing Then
        Call CheckAndChangeColor(prevCell)
    End If
    ' Update the previously active cell to the current selection
    Set prevCell = Target
End Sub

Sub CheckAndChangeColor(ByVal Target As Range)
    Dim cell As Range
    Application.EnableEvents = False ' Prevent recursive triggering
    On Error Resume Next ' Suppress potential errors
    
    ' Loop through each cell in the target range
    For Each cell In Target
        ' Save the current border settings
        Dim topBorder As Variant, bottomBorder As Variant, leftBorder As Variant, rightBorder As Variant

        topBorder = cell.Borders(xlEdgeTop).LineStyle
        bottomBorder = cell.Borders(xlEdgeBottom).LineStyle
        leftBorder = cell.Borders(xlEdgeLeft).LineStyle
        rightBorder = cell.Borders(xlEdgeRight).LineStyle

        ' Change the color of the selected cells to white
        cell.Interior.Color = RGB(255, 255, 255)

        ' Restore the border settings with the specified RGB color
        cell.Borders(xlEdgeTop).LineStyle = topBorder
        cell.Borders(xlEdgeBottom).LineStyle = bottomBorder
        cell.Borders(xlEdgeLeft).LineStyle = leftBorder
        cell.Borders(xlEdgeRight).LineStyle = rightBorder

        cell.Borders(xlEdgeTop).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeBottom).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeLeft).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeRight).Color = RGB(219, 229, 241)
    Next cell
    
    On Error GoTo 0 ' Restore normal error handling
    Application.EnableEvents = True ' Re-enable events
End Sub

 

[vikosh]

Use sheet protection.
Before protecting the worksheet, unlock the cell housing the dropdown (along other editable cells)
Edit: This will obviously prevent the user from formatting all cells, not just the one you want.

Hi, this solution doesn't work for me.

 

[vikosh]

Paste the following into the WorkSheet module :

Option Explicit

Private prevCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    Call CheckAndChangeColor(Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if there was a previously active cell and it's no longer the active cell
    If Not prevCell Is Nothing Then
        Call CheckAndChangeColor(prevCell)
    End If
    ' Update the previously active cell to the current selection
    Set prevCell = Target
End Sub

Sub CheckAndChangeColor(ByVal Target As Range)
    Dim cell As Range
    Application.EnableEvents = False ' Prevent recursive triggering
    On Error Resume Next ' Suppress potential errors
   
    ' Loop through each cell in the target range
    For Each cell In Target
        ' Save the current border settings
        Dim topBorder As Variant, bottomBorder As Variant, leftBorder As Variant, rightBorder As Variant

        topBorder = cell.Borders(xlEdgeTop).LineStyle
        bottomBorder = cell.Borders(xlEdgeBottom).LineStyle
        leftBorder = cell.Borders(xlEdgeLeft).LineStyle
        rightBorder = cell.Borders(xlEdgeRight).LineStyle

        ' Change the color of the selected cells to white
        cell.Interior.Color = RGB(255, 255, 255)

        ' Restore the border settings with the specified RGB color
        cell.Borders(xlEdgeTop).LineStyle = topBorder
        cell.Borders(xlEdgeBottom).LineStyle = bottomBorder
        cell.Borders(xlEdgeLeft).LineStyle = leftBorder
        cell.Borders(xlEdgeRight).LineStyle = rightBorder

        cell.Borders(xlEdgeTop).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeBottom).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeLeft).Color = RGB(219, 229, 241)
        cell.Borders(xlEdgeRight).Color = RGB(219, 229, 241)
    Next cell
   
    On Error GoTo 0 ' Restore normal error handling
    Application.EnableEvents = True ' Re-enable events
End Sub

Thank you so much.

I pasted it and i don't get it how it works and how i have to adjust it for my workbook? Does it apply to the whole workbook?

Please elaborate for me - i haven't used Excel/VBA for a loooooong time and i don't remember anything! AAAA!

Thank you in advance.

 

[Logit]

Right click the sheet tab of the sheet to be affected.
Select "View Code".
Paste the macro code into the large white area on the right hand side.
Go back to the worksheet.
Click a cell and attempt to change it's interior color. Then hit ENTER