thanks for all your suggestions. I have implemented dave's suggestions
into my code but I still not get a value returned. The code now:

Sub IdenticalMinLimits()

Dim PHDRange As Range

Dim CellValuePHD

Dim PHDResult As Variant

' Fetch min value from PHD data sheet via a VLOOKUP

Windows("PHD_XANS_DATA_SORT.xls").Activate
Worksheets("PHD").Activate

CellValuePHD = Workbooks("PHD_XANS_SOL_Comparison").Sheets("Day
1").Range("S7").Value

Set PHDRange = Range(Cells(4, 1), _
Cells(400, 15))

PHDResult = Application.VLookup(CellValuePHD, PHDRange, 15, False)
If IsError(PHDResult) Then
MsgBox "it wasn't found" 'same as #n/a error
Else
MsgBox PHDResult
End If

Windows("PHD_XANS_SOL_Comparison").Activate
Worksheets("Day 1").Activate


End Sub

No luck using this so far!