VBA wont return cell value

[Aussie Grid]

I'm using the following code in a worksheet change event but it wont return the Target Offset value. However if I delete the linked value and type in Sun or any other day it finds it.

If Not Intersect(Target, Range("D8:D38")) Is Nothing Then
If Target.Value = Range("P40").Value Then
If Target.Offset(0, -2).Value = Range("Q40").Value Then
MsgBox Range("P40") & " cannot work on a " & Range("Q40")
End If
End If
End If

The offset column is
b3=a3(Date, 1/1/10 in A1, formated to ddd) =Fri
b4=b3+1(Sat)
b5=b4+1(Sun)
b6=b5+1(Mon)
etc
The offset target is to be checked against Q40 (Sun) or ideally checked against a range of days, say Q40:Q46, with the particular days that they cannot work. ie. Sun Tue Fri .

Any and all help or other ideas appreciated.

Aussie Grid

 

[HalfAce]

Hello Aussie Grid, welcome to the board.
If I understand your data layout description, the cell 2 columns to the left of your target cell is a formula. Therefore the value is not the same, only the return from the formula (or the text) of the cell is the same. (yes?)

To fix it should be as simple as changing your line:
If Target.Offset(0, -2).Value
To:
If Target.Offset(0, -2).Text

Hope it helps.

 

[Aussie Grid]

Thanks very much HalfAce, worked perfectly of course.

Embarassed Aussie Grid