QA program struggling with ever changing data references

For my project I am building a QA program in exce; to check the data of one sheet against the data of another sheet. The problem here is that one of the sheets is extracted from a database so the formatting and the location of everything is different then in the file I will be checking it against. Another caveat is that this program has to work across multiple workbooks.

My first attempt at this problem was to make sure data in both sheets was sorted similarly. This made it so that all of the data is alphabetically sorted on both sides by the lowest possible matching criteria. I then in turn created a new sheets with basic if statements to then check the data in the appropriate rows.

The problem herein lies with missing data. My current code when dragged down accurately identifies if the data in the corresponding table is correct but doesn't account for if an entire row is missing. It will return a fail for that row then every row below it which is unacceptable for the goal I am trying to accomplish. Also I feel as if the program is hardcoded excessively which leaves me open for problems down the road, if I send this program to lower level employees to run QA they won't know to change references in the program to get the correct answers.

So basically what I am asking is how would you code this so that if an error arose by a row missing it wouldn't destroy the rest of the QA and make it useless but instead recognize that that row is gone return a fail for that row and the corresponding data and move on.

Here is my code very macro recorded heavy, as I am a very new VBAer.

'''''''This section returns the heading of the row I am taking the data from''''
    Sheets("Ratings QA").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[8]"
''''''''This section returns title headings of each row to ensure they match with title headings from the master'''''''
Range("A2").Select
ActiveCell.FormulaR1C1 = _
    "=IF('Detailed Ratings'!R[15]C[8]=Sheet1!RC[8],Sheet1!RC[8],""Fail"")"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A400"), Type:=xlFillDefault
Range("B1").FormulaR1C1 = "=Sheet1!RC[8]"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:BI1"), Type:=xlFillDefault
Range("B2").Select
ActiveCell.FormulaR1C1 = _
    "=IF('Detailed Ratings'!R[15]C[8]=Sheet1!RC[8],""Pass"",""Fail"")"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B400"), Type:=xlFillDefault
Range("B2:B400").Select
Selection.AutoFill Destination:=Range("B2:BI400"), Type:=xlFillDefault
Range("B2:BI400").Select
   Cells.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Pass"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Bold = True
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 5287936
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""Fail"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Bold = True
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
End With
Selection.FormatConditions(1).StopIfTrue = False
    With ActiveWindow
    .SplitColumn = 0
    .SplitRow = 1
End With

One proposed solution that I thought might work but I am unsure how to code would be that if a fail was returned in the first row where title heading should be, would be to include a placeholder row. So instead of just putting fail it would fill that row with fail then skip that range of cells then the following row would continue down the range as if nothing had happened. It would return error and not destroy the QA any ideas as how I can add this as a condition of the if statement.

Thank you,