Complicated formatting

[aussiegirlone]

Below is a macro to insert a row formatted

Sub NewRow()
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "Total"
Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")"
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AD"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AE"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.Weight = xlThin

End Sub

After the macro above has completed, is it possible to add to this code to
find any empty rows below and automatically format them white. The last row
to find empty is AF32

 

[Daniel.C]

Try :

Sub NewRow()
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
n = Cells(Rows.Count, "AF").End(xlUp).Row + 1
Cells(n, "AC").Value = "Total"
Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")"
Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True
Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AD"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AD"), Cells(n, "AC")).Borders.Weight = xlThin
Union(Cells(n, "AE"), Cells(n, "AC")).Interior.ColorIndex = 42
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.LineStyle = xlContinuous
Union(Cells(n, "AE"), Cells(n, "AC")).Borders.Weight = xlThin

For i = n + 1 To 32
If Application.CountA(Rows(i)) = 0 Then
Rows(i).Interior.ColorIndex = 2
End If
Next i
End Sub

HTH
Daniel

 

[aussiegirlone]

It was worth the waiting, Thankyou so much! It works very well