An Expert called Dave assisted me with a request that I would like adjusted for the attached spreadsheet.
Basically the code from the original request shifted the values from a row down each time a cell changed from a different cells elsewhere in the spreadsheet.
This time I would like a the value in cell AW3 to shift down once an alert has occurred.
The alert has already been taken care of by another brilliant expert called akoster, so I shouldn't need help with the alert. I just need the value to shift down once the alert occurs.
The following is the code for the original shifting:
Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long
Dim lastRow As Long
Set targ = Range("Q3:T3") 'Watch these cells for changes
nCols = targ.Columns.Count
Call setConditionals 'just to ensure its initialized in the first row
Set rg = Range("B3:G3") 'Put changed values here, pushing old values down
Set rg2 = rg.Offset(0, -1).Resize(1, rg.Columns.Count + 2)
Application.EnableEvents = False
For j = 1 To nCols 'Watch columns Q through AC on row 3 for changes
If oldVal(j) <> targ.Cells(1, j) Then
'Shift data down
rg2.Copy rg2.Offset(-1, 0)
'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value 'Copy over just data in Q3:V3
rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value 'Copy over data in Q3:AC3
For i = 1 To nCols 'Capture values of data for next run
oldVal(i) = targ.Cells(1, i).Value
Call setConditionals 'to update the new row 3, as its now the new row
'put the rest of this after your loop in the worksheet_calculate subroutine - or call it when you want as a separate module, to pare down your data, as needed.
lastRow = Range("A" & Rows.Count).End(xlUp).Row
lastRow = IIf(lastRow < 1200, 1200, lastRow)
Range("A2500", Range("A" & lastRow)).EntireRow.Clear
Application.EnableEvents = True
I have attached spreadsheet that needs the additional added to it.
As always, thank you in advance.