Conditional format based off spill formula value

[MiniFav]

I have an error check sheet within a work book, for example

Aircraft AD template V3 (In works).xlsx
	E	F
1	PART NUMBER	SERIAL (IF APPLIC')
2	12345-6789	112233
3	12345-6789
4	9876-54321
AD Initial

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F4	Expression	=IF('Error Check'!AR3<>"","TRUE","FALSE")	text	NO

Now this works great, but I have times where a new row needs to be inserted within the data.

upon inserting a row between rows 3 and 4 i get this.

Aircraft AD template V3 (In works).xlsx
	E	F
1	PART NUMBER	SERIAL (IF APPLIC')
2	12345-6789	112233
3	12345-6789
4
5	9876-54321
AD Initial

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F4	Expression	=IF('Error Check'!AR3<>"","TRUE","FALSE")	text	NO

Row 4 looks fine, no part number so doesnt highlight the serial number, but row 5 has now been bumped down to where there is a part number but the conditional formatting doesnt move along to show a serial number is required. Further more if i populate E4 with a part number then both F4 and F5 turn red and F5 remains red even if i remove the value within E5.

Like so.

Aircraft AD template V3 (In works).xlsx
	E	F
1	PART NUMBER	SERIAL (IF APPLIC')
2	12345-6789	112233
3	12345-6789
4	12345-6790
5
AD Initial

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F4	Expression	=IF('Error Check'!AR3<>"","TRUE","FALSE")	text	NO

[XR]


For additional information, this is the error sheet cells i refer to.

Aircraft AD template V3 (In works).xlsx
	AR
2	If part number then serial required
3
4	Serial number required
5	Serial number required
Error Check

Cell Formulas
Range	Formula
AR3:AR6	AR3	=IF('AD Initial'!E2:E5<>"",IF('AD Initial'!F2:F5="","Serial number required",""),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AR2	Expression	=COUNTIFS(AR3:AR1001,"")<>999	text	NO

 

[Fluff]

Why not just use

=AND(E2<>"",F2="")

rather than looking at another sheet.

 

[MiniFav]

Why not just use

=AND(E2<>"",F2="")

rather than looking at another sheet.

I was hoping to have one sheet just for all errors with text so a user can look and see whats wrong, but if its messing up conditional formatting i may have to use both methods.

 

[Fluff]

You can still have the sheet with the error messages, but as that just looks at the AD Initial sheet, it makes sense to make the CF rule on that sheet use the same criteria as your formula on the Error sheet.