Help with FILTER & SPILL and formatting...

[MeaclH]

Hi all,

I have a sheet I am working on that has a formula working perfectly. It cross checks the values in the column versus values required on a different sheet. For any values that aren't there, they are returned and spilt into the cells below. This is perfect.

However, as part of our work flow, when we have verified that the value is not required, I have a VBA that marks the value as crossed out. You will see the formatting change in column S5 is now plain black text as opposed to the bold red in others.
The problem I have, is the values in the column are changed regularly, so once I have changed the formatting of S5, and another value is removed from the column, that value is put in the top row, and pushes the others ones down. The formatting remains in S5 rather than sticking with the value.

I don't think I have explained that very well. I hope you can follow what I am asking for Essentialy, I'd like to keep the formatting with the value, rather than the cell. Or if you have any other suggestions on how to reflect the values that have been cross checked.

Formula in cell

=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(S11:S103,DATA!$AM$3:$AM$19)))

VBA used to format cells we have double checked.

Sub MarkShiftCovered()
     Dim sCmt As String
    Dim rCell As Range
    
    sCmt = InputBox( _
      Prompt:="Have you covered this shift in it's entirety?" & vbCrLf & _
      "Please add details of how the shift has been covered. ie. OFF roster, extensions.", _
      Title:="Comment to Add")
    If sCmt = "" Then
        MsgBox "Not actioned. Has the shift been covered?"
    If StrPtr(sCmt) = 0 Then Exit Sub 'user cancelled inputbox
            Else
        For Each rCell In Selection
            With rCell
                ActiveSheet.Unprotect
                .ClearComments
                .AddComment
                .Comment.Text Text:=sCmt
            End With
        Next
    End If
    Set rCell = Nothing
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.ColorIndex = xlNone
Selection.Font.Underline = False
Selection.Font.Bold = False
Selection.Font.Strikethrough = True
ActiveSheet.Protect DrawingObjects:=False
End Sub

DIGITAL ROSTER MACH2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	FORTNIGHT COMMENCING		UNPUBLISHED				SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3							1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec	11-Dec	12-Dec	13-Dec	14-Dec
4	01-Dec-24		Employee Name				Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts	Open Shifts
5							#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	#CALC!	0530TL	1330TL
6																			1330TL
7
8
9
10	DAO Team Leaders
11	Line 1	Rostered T/L	DONMEZ. K			Shift	0530TL	0530TL	0530TL	0530TL	0530TL	OFF	OFF	OFF	OFF	OFF	0530TL	0530TL		0530TL
12		Original T/L	DONMEZ. K			Extension
13		Remarks			####	Remarks
14	Line 2	Rostered T/L	CUGLIARI. D			Shift	1330TL	1330TL	OFF	OFF	1330TL	1330TL	1330TL	1330TL	1330TL	1330TL	1330TL	OFF	OFF	OFF
15		Original T/L	CUGLIARI. D			Extension
16		Remarks			9	Remarks
17	Line 3	Rostered T/L	CROCE. B			Shift	OFF	OFF	1330TL	1330TL	2130TL	2130TL	2130TL	2130TL	2130TL	OFF-U	OFF-U	OFF-U	A/L	A/L
18		Original T/L	CROCE. B			Extension
19		Remarks			9	Remarks
20	Line 4	Rostered T/L	ABDULRAHMAN. M			Shift	2130TL	2130TL	2130TL	2130TL	OFF	OFF	OFF	OFF	OFF	2130TL	2130TL	2130TL	2130TL	2130TL
21		Original T/L	ABDULRAHMAN. M			Extension
22		Remarks			9	Remarks
23	Line 5	Rostered T/L	PAPARIS. M			Shift	OFF	OFF	OFF	0700AD	0700AD	0530TL	0530TL	0530TL	0530TL	0530TL	OFF	1330TL	1200AD	OFF
24		Original T/L	PAPARIS. M			Extension
25		Remarks			9	Remarks
FN 01DEC-14DEC24

Cell Formulas
Range	Formula
G3	G3	=IF(A4="","",A4)
H3:T3	H3	=IF(G3="","",G3+1)
G5,M5:N5,T5	G5	=FILTER(DATA!$AL$3:$AL$17,NOT(COUNTIF(G11:G103,DATA!$AL$3:$AL$17)))
H5:L5,O5:R5,S5:S6	H5	=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(H11:H103,DATA!$AM$3:$AM$19)))
C11,C23,C20,C17,C14	C11	=IF($A$4="","",VLOOKUP(CONCATENATE(VALUE($A$4),$A11),DATA!$U$3:$V$1568,2,0))
C12,C24,C21,C18,C15	C12	=IF($A$4="","",VLOOKUP(CONCATENATE(VALUE($A$4),$A11),DATA!$U$3:$V$1568,2,0))
E13,E25,E22,E19,E16	E13	=AR11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T	Expression	=G$3=TODAY()	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	ending with "?"	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	contains "OK"	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	contains "DEC"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "GAZETTE"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "AV"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "EDO-U"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "OFF-U"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "BLV"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "CTFN"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "CDO"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "STFN"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "SDO"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "LSL"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "A/L"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "PHC"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "OFF"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "EDO"	text	NO
G5:T5	Cell	contains an error	text	NO
E13,E16,E19,E22,E25	Cell Value	>=12	text	NO
E13,E16,E19,E22,E25	Cell Value	=11	text	NO
E13,E16,E19,E22,E25	Cell Value	=10	text	NO
E13,E16,E19,E22,E25	Cell Value	=9	text	NO
C2	Cell Value	contains "UNPUBLISHED"	text	NO
C2	Cell Value	contains "PUBLISHED"	text	NO
C12,C15,C18,C21,C24,C28,C43,C58,C73,C31,C46,C61,C76,C34,C49,C64,C79,C37,C52,C67,C82,C40,C55,C70,C85	Expression	=IF(C12=C11,TRUE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C11:E11	List	=DATA!$AB$4:$AB$12
C14:E14	List	=DATA!$AB$4:$AB$12
C17:E17	List	=DATA!$AB$4:$AB$12
C20:E20	List	=DATA!$AB$4:$AB$12
C23:E23	List	=DATA!$AB$4:$AB$12
G17:T17	List	=DATA!$AF$4:$AF$49
G11:T11	List	=DATA!$AF$4:$AF$49
G14:T14	List	=DATA!$AF$4:$AF$49
G20:T20	List	=DATA!$AF$4:$AF$49
G23:T23	List	=DATA!$AF$4:$AF$49
C2:E3	List	=DATA!$AJ$4:$AJ$5
A4:B9	List	=DATA!$W$4:$W$70