IF Blank Range of cells

[rbolinsky]

So I need to populate today's date in Cell A4 based off any value that may be manually entered in cell b4, c4, d4, e4. So if B4 had $2131, A4 would get today's date populated. However if B4 had the same $2131 and C4 had $2344 then it would take the date that C4 gained the value. So it would always reflect the date last paid and not a moving target. Any ideas?

What I want is the last paid date of an incentive bonus. Picture for reference.

 

[Jamie McMillan]

Hello,

First set the format of B4:E4 as currency - do not type in the "$" sign or Excel reads it as text, not a number.

Press Alt F11 and paste the code below.

Sub InsertDate()
    If Range("C4").Value = 0 Then 'If C4 is empty - it will do this bit
    Range("A4").Value = "=TODAY()" 'Inserts todays date
    Range("A4").Copy 'Copies the cell A4
    Range("A4").Select 'Pastes the value in A4, so it does not automatically update
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
    If Range("D4").Value = 0 And Range("C4").Value > Range("B4").Value Then 'If D4 is empty AND C4 is greater than B4 - it will do this bit
    Range("A4").Value = "=TODAY()"
    Range("A4").Copy
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
    If Range("E4").Value = 0 And Range("D4").Value > Range("C4").Value Then
    Range("A4").Value = "=TODAY()"
    Range("A4").Copy
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Else
    If Range("E4").Value > Range("D4").Value Then
    Range("A4").Value = "=TODAY()"
    Range("A4").Copy
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
    End If
    End If
    End If
    Application.CutCopyMode = False
End Sub

Now right click on the sheet tab and select View Code; and paste the code below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$4" Then 'when each cell changes it calls the code above
        Call InsertDate
    End If
    If Target.Address = "$C$4" Then
        Call InsertDate
    End If
    If Target.Address = "$D$4" Then
        Call InsertDate
    End If
    If Target.Address = "$E$4" Then
        Call InsertDate
    End If
End Sub

Jamie