Excel VBA Help

[Sooner4Life]

I have an excel sheet setup to track quarterly taxes owed. I currently use VBA code to date stamp one column when another is not blank and that works fine but I would like to change it to the code below. The only problem is all the ranges listed have formulas "=IFERROR(IF(SUM(D4:E4)>0,IF(D4<>"",(D4*$AC$25))+IF(E4<>"",(E4*$AC$26)),""),"")" so the date stamp never triggers . Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F4:F104,L4:L104,R4:R104,X4:X104"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -4).ClearContents
Else
With .Offset(0, -4)
.NumberFormat = "mm/dd/yyyy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
With ActiveSheet
.Protect AllowFiltering:=True

If Target.Cells.Count > 1 Then

Exit Sub

End If

End With

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim f As Range
 
  If Target.Count > 1 Then Exit Sub
 
  ActiveSheet.Unprotect
  If Not Intersect(Range("F4:F104,L4:L104,R4:R104,X4:X104").DirectPrecedents, Target) Is Nothing Then
 
    Application.EnableEvents = False
   
      Set f = Target.DirectDependents
      With f.Offset(0, -4)
        If f.Value = "" Then
          .ClearContents
        Else
          .NumberFormat = "mm/dd/yyyy"
          .Value = Now
        End If
      End With
     
    Application.EnableEvents = True
  End If
  ActiveSheet.Protect AllowFiltering:=True
End Sub

I explain how it works:

First, I assume the formula is in cell F4​

1. This means

If Not Intersect(Range("F4:F104,L4:L104,R4:R104,X4:X104").DirectPrecedents, Target) Is Nothing Then​

that if you modify any cell that is inside this formula:​

"=IFERROR(IF(SUM(D4:E4)>0,IF(D4<>"",(D4*$AC$25))+IF(E4<>"",(E4*$AC$26)),""),"")​

Then the event will triggered​

2. This means

Set f = Target.DirectDependents​

Which will get the cell where the affected formula is located.​

​

For example, if you modify the cell D4, then in the object 'f' we will have cell F4.​

With cell F4 we can then offset 4 cells to the left and enter the date.​

3. This means

If f.Value = "" Then​

Checks if the cell contains "" as a result of the formula, unlike IsEmpty which checks if the cell is completely empty.​

4. I'm also assuming you have a similar formula in cells L4, R4 and X4.

Important! Consider that if you modify cells $AC$25 or $AC$26, it implies that the 4 formulas will be affected, therefore the 4 cells on the left will be modified.​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --