Hi:

Toppers has given you a good solution.

To check if the code is in the correct place in the worksheet where the data
validation is right click the sheet tab at the bottom of the page and select
view code that is the place to put it.

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


"Toppers" wrote:

In VBE, select the sheet where you have your Data Validation(s) e.g Sheet1
(in the Project Window), right click and "View Code". Copy the code into this
sheet as this a worksheet event macro which needs to be associated with a
sheet (as opposed to a general macro which appears in (for example)
"Module1").

HTH

"Alison84" wrote:

Sorry but I don't quite understand what you mean. I copied the text and
pasted it as a VBA macro (if that was what you wanted me to do) but it didn't
work.

"Toppers" wrote:

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 :)