Assign a word to a range between two dates VBA

[Isaac21]

Hello, I am attempting to assign the value "Senior" to the number difference between two dates in vba, as seen in the code below

basically im looking to assign a word to a range between two dates

but this vba code is not working, no error shows but it wont populate the cell in question

All help is appreciated
Thanks
Isaac

Range("D2").Select
If (ActiveCell.Value > 1 / 1 / 1940) And (ActiveCell.Value < 1 / 1 / 2003) Then
    Range("J2").Value = "Senior"
    Else
    Range("J2").Value = ""
End If

 

[Zot]

You don't need to select. Avoid it unless no choice

If Range("D2") > DateValue("1 / 1 / 1940") And Range("D2") < DateValue("1 / 1 / 2003") Then
    Range("J2").Value = "Senior"
    Else
    Range("J2").Value = ""
End If

 

[Isaac21]

You don't need to select. Avoid it unless no choice

If Range("D2") > DateValue("1 / 1 / 1940") And Range("D2") < DateValue("1 / 1 / 2003") Then
    Range("J2").Value = "Senior"
    Else
    Range("J2").Value = ""
End If

Thanks, I have a long list of data, useing this it only applies a name to the range from D2, how would i get it to go though a list of data and apply the name (eg D3, D4)

 

[Zot]

You can define range and loop through the range. Something like this

Dim cell As Range, rngD As Range

If cell > DateValue("1 / 1 / 1940") And cell < DateValue("1 / 1 / 2003") Then
    Range("J2").Value = "Senior"
    Else
    Range("J2").Value = ""
End If

 

[Isaac21]

You can define range and loop through the range. Something like this

Dim cell As Range, rngD As Range

If cell > DateValue("1 / 1 / 1940") And cell < DateValue("1 / 1 / 2003") Then
    Range("J2").Value = "Senior"
    Else
    Range("J2").Value = ""
End If

As you can tell I'm new to this, but when i try and run the new code it comes up with Run-time error '91": "Object variable or with block variable not set" any ideas?

 

[Zot]

My bad. I did not loop anything.... hold only. I write step by step

 

[Zot]

Dim eRow&
Dim cell As Range, rngD As Range
Dim ws As Worksheet

' Assuming your worksheet name is Sheet1
Set ws = ActiveWorkbook.Sheets("Sheet1")

' Find last cell with data in column D and define range
Set cell = ws.Range("D2").End(xlDown)
Set rngD = ws.Range("D2", cell)

' Loop through range D2 to D last row
For Each cell In rngD
    If cell > DateValue("1 / 1 / 1940") And cell < DateValue("1 / 1 / 2003") Then
        Range("J" & cell.Row).Value = "Senior"
    Else
        Range("J" & cell.Row).Value = ""
    End If
Next