Format Formula Reference To Different Sheet

I have a workbook with several sheets, this question focuses on two sheets. 'Coded Prehospital Data' and 'Missing Pre Hospital Data', for brevity here i will refer to them as coded and missing respectively.

In coded the cells are populated using an index/match fx to another sheet and if that returns an error the fx looks to the same cell in missing for a value

Here is an example of the formula in coded cell C2:

=IF(OR(ISERROR(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE))),
ISBLANK(INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))),VLOOKUP(
'Missing Pre Hospital Data'!D2,AGEUNITSCODE,2,FALSE),INDEX(MATCHAGEUNIT,MATCH(A2,MATCHCC,FALSE)))

Without having to constantly switch between sheets I would like to set up a conditional format that fills a cell in missing yellow when that cell is an error in coded.

This is what I have done to achieve this with no success:

1) Select cell C2 in missing and add rule based on formula
2) Enter =OR(ISNA('CODED PREHOSPITAL DATA'!$C2),ISBLANK('CODED PREHOSPITAL DATA'!$C2))
3) Enter the custom formatting I decided on
4) In the "Applies To" box I have done two things: 1) drag the cursor from c2 to an28 which auto fills the applied to dialogue box with 'Missing Prehospital Data'!$C$2:$AN$28 and 2) Free type in the dialogue box 'Missing Prehospital Data'!$C2:$AN28

My problem is that I need the formatting for each cell in missing to refer to its sister cell in coded but it continues to refer only to coded c2