Edit Cells By Comparing Value

[JLandrum]

Please Help, I have attached the doc.
What im looking for is for the cells highlighted in yellow to have a msg of NH if the date (D.O.H.) is greater than cell B4. Any help is greatly appreciated, thank you.
I can do it excel function but would like it done in VBA. Thanks again.

[mperrah]

Sub probation90()
Dim x, lr, c As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
c = 0
    For x = 11 To lr
        If Cells(x, 3).Value > Cells(4, 2).Value Then
        Cells(x, 4).Value = "NH"
        c = c + 1
        End If
    Next x
    MsgBox ("There are " & c & " NH employees as of " & Now())
End Sub

similar to last post

[mperrah]

vba52574.xlsm
from excel type Alt+F8 then choose "probation90" and click run

[JLandrum]

Thank you that does work but is there a way to make it auto populate without Alt+F8

[mperrah]

Add this to the worksheet code
and doubleclick any where to see reults:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim x, lr, c As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
c = 0
    For x = 11 To lr
        If Cells(x, 3).Value > Cells(4, 2).Value Then
        Cells(x, 4).Value = "NH"
        c = c + 1
        End If
    Next x
    MsgBox ("There are " & c & " NH employees as of " & Now())
End Sub

[mperrah]

vba52574.xlsm
just alter a few dates in your hired date column and double click a cell (anywhere)

[mperrah]

This adds a clearcontents for the column so changes are easier to see as you test

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim x, lr, c As Long

lr = Cells(Rows.Count, 3).End(xlUp).Row
Range("D11:D" & lr).ClearContents
c = 0
    For x = 11 To lr
        If Cells(x, 3).Value > Cells(4, 2).Value Then
        Cells(x, 4).Value = "NH"
        c = c + 1
        End If
    Next x
    MsgBox ("There are " & c & " NH employees as of " & Now())
End Sub

[mperrah]

be sure to double click a cell after you update values

[mperrah]

vba52574v3.xlsm

[JLandrum]

Thanks works great

[mperrah]

glad to help,
you can add a command button and paste the macro to that if you don't mind buttons.
I thought the double click was less intrusive on your page layout.
cheers
-mark