VBA Code referring to a cell formula

I will try and explain this the best I can, not my strongest point

I have the following VBA code in excel which works fine if the value in C14 has the number 1 inside it. The rows hide fine.

However if I have a formula in cell C14 which is either =Sheet2!B6 or a lookup then it no longer works. I have been googling all over but cannot find anything, to be honest I am not sure what I should be googling for!

Any help before my hair gets even more grey in it would be welcome

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$14" Then
If Target. Value = 1 Then
Rows("42:58").Select
Selection.EntireRow.Hidden = True
Else
Rows("42:58").Select
Selection.EntireRow.Hidden = False
End If
End If
End Sub