"Find" fails "date" search

[SeniorTom]

I search row 4 for a date m/d/yyyy. I have 2 cells that look the same, but have a different formula. Below CT4 & CU4 the “find” works, but CV4 fails. My failed cells were initially referenced to another sheet, then copy/paste values. I have reentered number format/date with no change. If I reenter the date, it finds it.

I have the work around, but would like to know what cause the difference. Any help is appreciated.

'tries to find target date in fourth row
Set FoundIt = .Range("4:4").Find(What:=NewDate, LookAt:=xlWhole, MatchCase:=False)

If FoundIt Is Nothing Then
GoTo ErrMsg
End If

Using this code I check these properties:
"Formula" is different, but I can’t find the discrepancy

Sub DispProperty()
Dim cell As Range
Set cell = Cells(4, 100) ' You can change this cell as needed
Dim msg As String

msg = "Cell Address: " & cell '.Address & vbCrLf
msg = msg & "Value: " & cell.Value & vbCrLf
msg = msg & "Font Name: " & cell.Font.Name & vbCrLf
msg = msg & "Font Size: " & cell.Font.Size & vbCrLf
msg = msg & "Font Color: " & cell.Font.Color & vbCrLf
msg = msg & "Background Color: " & cell.Interior.Color & vbCrLf
msg = msg & "Formula: " & cell.Formula & vbCrLf
msg = msg & "Number Format: " & cell.NumberFormat & vbCrLf

MsgBox msg, vbInformation, "Cell Properties"
End Sub



CT4 works

CU4 Works

CV4 fails

 

[SeniorTom]

OOPs left this out.

 

[MARK858]

I have reentered number format/date with no change. If I reenter the date, it finds it.

That says to me that the cell was originally text and not a real date, changing the number format after the entry was made wouldn't change that.