Hi Tom,

I could have sworn I tried that, but surely not as that works a treat, thank
you as always.

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C28" Then
If InStr(1, Range("C28"), "note", vbTextCompare) Then
Range("F28,G28,H28,I28") = "N/A"
Else
If Range("F28,G28,H28,I28") = "N/A" Then
Range("F28,G28,H28,I28").ClearContents
End If
End If
End If
If Target.Address(False, False) = "C29" Then
If InStr(1, Range("C29"), "note", vbTextCompare) Then
Range("F29,G29,H29,I29") = "N/A"
Else
If Range("F29,G29,H29,I29") = "N/A" Then
Range("F29,G29,H29,I29").ClearContents
End If
End If
End If
End Sub

this statement:
If Range("F28,G28,H28,I28") = "N/A" then

is a bit peculiar
Just do demonstrate from the immediate window:
Range("F28") = "N/A"
Range("G28,H28,I28").ClearContents
? Range("F28,G28,H28,I28") = "N/A"
True
Range("F28").ClearContents
Range("G28,H28,I28")= "N/A"
? Range("F28,G28,H28,I28") = "N/A"
False

so we see that it returns true or false solely on the value of the first
cell, F28.

that may or may not be what you want.

--
Regards,
Tom Ogilvy





"Newbeetle" wrote:

Hi I have modified the code below to;

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C28" Then
If InStr(1, Range("C28"), "note", vbTextCompare) Then
Range("F28,G28,H28,I28") = "N/A"
Else
If Range("F28,G28,H28,I28") = "N/A" Then
Range("F28,G28,H28,I28").ClearContents
End If
End If
End If
End Sub

With all the help thats been given I'm glad to say it works a treat, I would
now like to do the same for cell C29, to return to F29,G29,H29,I29 but have
got a little stuck.

"JE McGimpsey" wrote:

Change the line in the second event macro from

If Target.Address = "C28" Then

to

If Target.Address(False, False) = "C28" Then


In article ,
Newbeetle wrote:

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