Hi Tom,

I copied the code into a completly new workbook and placed it in Excel
objects sheet one, I then went to the Excel sheet and typed note in Cell C28,
but the cell D28 stays blank.

Any futher ideas appreciated.

"Tom Ogilvy" wrote:

You would need to run a macro which is written in VBA.

Right click on the sheet tab and select view code.

Put in code like this:

Private Sub Worksheet_Calculate()
If InStr(1, Range("C28"), "note", vbTextCompare) Then
Range("D28") = CVErr(xlErrNA)
Else
If Range("D28") = CVErr(xlErrNA) Then
Range("D28").ClearContents
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "C28" Then
If InStr(1, Range("C28"), "note", vbTextCompare) Then
Range("D28") = CVErr(xlErrNA)
Else
If Range("D28") = CVErr(xlErrNA) Then
Range("D28").ClearContents
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Newbeetle" wrote in message
...
Hi, I would like to carry out an IF function so that if one cell has a
text
of " Note", text of "N/A" is put in another cell ie =IF(C28="Note","N/A",
""), but I want to do this in VBA so that I can still type in the cell
that
the answer would be put into, ie if the text isnt "Note" I want to be able
to
manually write other text or values in the cell. I want this to be
automatic
without running macros, so that "N/A automatically appears if "Note" is
written is the reference cell.
VBA is not my strong point but I'm guessing that an IF statement can be
used
on the correct sheet in Excel objects.