Try putting this code in the sheet where you have your condition:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.And(Target = Range("$C$1"), Target.Value < "") Then
Range("A1") = "Complete"
End If
End Sub

HTH

"Alison84" wrote:

Hmmm...yes and no.

I have a sheet of tasks for different departments, some of the columns are
as follows; Status, Estimated completion date, revised completion date, date
completed.

In the Status I only want them to be able to write open, complete or not
started and if they put a date in the date completed column I want it to
automatically write "Complete" in the status column as this is what they
always forget to do - which leaves me having to check. It will also kick in
my other conditional format that colours it blue when it is complete
automatically.

So can it be done?

"Martin Fishlock" wrote:

Alison,

This may sound a little silly, but can't you use another cell to say that it
is complete or turn off data validation for that cell?

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Alison84" wrote:

Hi,

I am trying to write a formula that will return the word Complete in cell A1
if there is any value in cell C1.

I can do this with this formula =if(not(isblank(c1)), "Complete", "")

But... column A has data validation so I can't write the formula in. When I
write this formula in the conditional formatting formula is box it doesn't
accept it.

Can anyone help??

Thanks :)