OK, here's a first draft. Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. On the left side navigation tree, double click on the ThisWorkbook item:
View attachment 93121
Paste the following code into the window that opens:
VBA Code:
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Cell").Reset ' When exiting, reset the menu
On Error GoTo 0
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cmdBtn As CommandBarButton, cntl As Variant
On Error Resume Next
Application.CommandBars("Cell").Reset ' Reset the menu
' These lines limit the menu changes to a specific sheet and range
If ActiveSheet.Name <> "Scheduling" Then Exit Sub ' Right sheet?
If InStr("CDEFHIJK", Left(Target.Address(0, 0), 1)) = 0 Then Exit Sub ' Right columns?
If Target.Row < 3 Or Target.Row > 34 Then Exit Sub ' Right rows?
With Application.CommandBars("Cell").Controls.Add(Temporary:=True, before:=1)
.Caption = "Mark Shift Available" ' Caption description
.Style = msoButtonCaption
.OnAction = "AddShift" ' Macro to call
End With
On Error GoTo 0
End Sub
Change the sheet name about halfway down to match your workbook. (I called the sheet "Scheduling", I'm sure you have a different name.) Next press Alt-IM to Insert a Module. Paste the following code into that window:
VBA Code:
Public Function AddShift()
Dim r As Long, c As Long
ActiveCell.Interior.Color = vbYellow
r = Range("M1000").End(xlUp).Row + 1
Cells(r, "M") = Format(Cells(ActiveCell.Row, "B"), "dddd, d mmmm yyyy")
c = IIf(ActiveCell.Column < 7, 3, 8)
Cells(r, "N") = Cells(2, ActiveCell.Column) & " " & Cells(1, c)
End Function
Now close the VBA editor, go back to Excel, and right-click one of the cells in the right range. You should see "Mark Shift Available" at the top of the menu. Select it, and the cell should turn yellow, and the data is entered in the next available row in the M:N columns.
As far as Nicole, I'm a bit confused as to what you want. The list in the O:P column seems to be all of the dates Nicole is scheduled. Did you manually enter those, or does the "Update" button do that? You could have an Event routine to do that automatically when you enter Nicole in the C:K columns. But however you get her dates in the O:P columns, what do you want to do if you right click a cell with Nicole in it? Delete the data from the O:P column? Leave it there and highlight it yellow?
Let me know!