Selected Answer
Leap
Not sure why you want to do this (as per my discussion point) but the values in column E can be replaced by "-" using the slightly modified code shown below:
Private Sub DashE()
Dim lr As Integer
Dim c As Range
lr = Cells(Rows.Count, 5).End(xlUp).Row
Set c = Range("E2:E" & lr)
For Each Bal In c
If Range("a" & Bal.Row) < Date Then
Bal.Value = "-"
Bal.HorizontalAlignment = xlCenter
Bal.Interior.Color = vbGreen 'set the color to green (since it's not a real balance)
End If
Next Bal
End Sub
(I got a strange error on your workbook so created an similar one and renamed the Sub).
The variable "lrl" isn't needed in your code but "lr" is used to see where column E ends and to set your range "c". I've add a variable "Bal" to represent a balance cell in the range c (then loop through c). If the date is less than today in column A, the macro change the balance to "-" but also sets the cell colour to green (to indicate that something is a bit odd).
Below is similar code but triggered by the Worksheet_Change event (if cell i1 is chnaged or entered as "show", but nothing else). It returns the formula to any cell containing "-" and changes the colour back to clear Key bits are in bold below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("i1")) Is Nothing Or Range("i1") <> "show" Then Exit Sub
Dim lr As Integer
Dim c As Range
lr = Cells(Rows.Count, 5).End(xlUp).Row
Set c = Range("E2:E" & lr)
For Each Bal In c
If Bal.Value = "-" Then
Bal.Formula = "=C" & Bal.Row & "-D" & Bal.Row
Bal.Interior.Color = vbWhite 'reset to clear
End If
Next Bal
End Sub
Both these macros are in the revised file attached (you'll need to run DashE from VB Explorer).
Hope this is what you wanted.
