PDA

View Full Version : [SOLVED] Edit Cells By Comparing Value



JLandrum
05-13-2015, 12:39 PM
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
05-13-2015, 01:04 PM
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
05-13-2015, 01:06 PM
13370
from excel type Alt+F8 then choose "probation90" and click run

JLandrum
05-13-2015, 01:24 PM
Thank you that does work but is there a way to make it auto populate without Alt+F8

mperrah
05-13-2015, 01:47 PM
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
05-13-2015, 02:13 PM
13371
just alter a few dates in your hired date column and double click a cell (anywhere)

mperrah
05-13-2015, 02:19 PM
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
05-13-2015, 02:26 PM
be sure to double click a cell after you update values

mperrah
05-13-2015, 02:33 PM
13372

JLandrum
05-13-2015, 02:36 PM
Thanks works great

mperrah
05-13-2015, 02:42 PM
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