Validation input text Col C conditional on text in Col B

[Ironman]

Hi

I have the following code that I want to run once in Col C of sheets 'Training Log' and 'Training 1981-1997', which creates a validation input msg ""Double click for lifetime mileage total up to this date" in the rows where Col 9 starts with the text string "Day"

Sub InsertValidationInput
If ActiveSheet.Name = "Training Log" and "Training 1981-1997" and If target.Column = C And Left(target.Value, 3) = "Day" Then
Application.EnableEvents = False
With Selection.Validation
          .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
          :=xlBetween
          .IgnoreBlank = True
          .InCellDropdown = False
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = "Double click for lifetime mileage total up to this date"
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
         Application.EnableEvents = True
        End With
End Sub

Can you please tell me if there are any obvious errors in this code?

Many thanks!

 

[NoSparks]

Good evening Paul,

Shouldn't the ActiveSheet.Name = "Training Log" OR "Training 1981-1997"

 

[Ironman]

Hi Nolan

Yes, I've just noticed that, thanks.
I changed it to this:

Sub InsertValidationInput()
If ActiveSheet.Name = "Training Log" Or "Training 1981-1997" Then
If target.Column = 3 And Left(target.Value, 3) = "Day" Then

but I get a "type mismatch" error 13 with row

If ActiveSheet.Name = "Training Log" Or "Training 1981-1997" Then

 

[NoSparks]

try either target.column = "C" or target.column = 3

 

[NoSparks]

If ActiveSheet.Name = "Training Log" Or ActiveSheet.Name ="Training 1981-1997" Then

 

[Ironman]

I've just amended it again

Sub InsertValidationInput()
If ActiveSheet.Name <> "Training 1981-1997" And ActiveSheet.Name <> "Training Log" Then
      MsgBox "This function will only run in Training Log sheets", vbInformation, "Function Invalid in This Sheet"
      Exit Sub
   End If

If target.Column = 3 And Left(target.Value, 3) = "Day" Then

But it errors "Object required" on this row

If target.Column = 3 And Left(target.Value, 3) = "Day" Then

 

[NoSparks]

This isn't an event... you don't have a target

You're going to need to specify the range to work on. Originally you had Selection.

 

[Ironman]

OK thanks but I'm afraid I don't know how it should be written instead! It should be inserting the validation input text in rows in Column 3 where Column 9 begins with the text "Day"

 

[Ironman]

I've got this far now

With ActiveSheet.Name
If Range = "C" & Left(Cell.Value, 3) = "Day" Then

But I'm getting error "Compile error argument not optional" with the word "Range"

 

[NoSparks]

Try this:
I don't know what row you want to start at or what column this is to go in (hence the ???) not sure if this requires events to be disabled either.
But think it will give you the idea about looping down the C column.

Sub InsertValidationInput()

    Dim i As Long, LastRow As Long

If ActiveSheet.Name <> "Training 1981-1997" And ActiveSheet.Name <> "Training Log" Then
    MsgBox "This function will only run in Training Log sheets", vbInformation, "Function Invalid in This Sheet"
    Exit Sub
End If

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For i = ??? To LastRow
    If Left(Cells(i, 3).Value, 3) = "Day" Then
        With Cells(i, ???).Validation
            ' and the rest 
            '

Hope that helps
Half past ten here... I'm signing off and going to bed.
Good Luck