Forum Discussion
Can a Macro be created for this?
I'm running Windows 10, Excel 2013
In the attached example, I'd like to be able to run a macro that runs down Column F from the top to bottom (which is over 300 rows) and insert a row and a formula such as is called out in the example to determine the delta between the dates throughout the column. If that number is less than 4, then the resulting cells should display red.
There will be blanks every 6 or so rows, such as showing in rows 24 and 32 in the example; which in these cases, no action need be taken. Is this possible? Any help is greatly appreciated.
7 Replies
Sub dates() Dim i As Integer Dim lngzeilemax As Integer lngzeilemax = Cells(Rows.Count, 6).End(xlUp).Row For i = 2 To lngzeilemax If Cells(i, 6) = "" Then If Application.WorksheetFunction.Days(Cells(i + 1, 6), Cells(i - 1, 6)) < 4 And Application.WorksheetFunction.Days(Cells(i + 1, 6), Cells(i - 1, 6)) >= 0 Then Cells(i, 6).Value = Application.WorksheetFunction.Days(Cells(i + 1, 6), Cells(i - 1, 6)) Cells(i, 6).NumberFormat = "0" Cells(i, 6).Interior.ColorIndex = 3 Else If Application.WorksheetFunction.Days(Cells(i - 1, 6), Cells(i + 1, 6)) < 4 And Application.WorksheetFunction.Days(Cells(i - 1, 6), Cells(i + 1, 6)) >= 0 Then Cells(i, 6).Value = Application.WorksheetFunction.Days(Cells(i - 1, 6), Cells(i + 1, 6)) Cells(i, 6).NumberFormat = "0" Cells(i, 6).Interior.ColorIndex = 3 Else End If End If Else End If Next i End SubMaybe with this code. Click the button in cell I2 in the attached file to start the macro.
- Thank you!
I tested it out and it works on the sheet you attached. I am new to this but a fast learner. The only thing, it only looks at dates above and below blank rows. How can it enter blank rows automatically?- Or, I will need another macro to insert the lines...?
