Complicated formatting
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 |
Complicated formatting
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 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 |
Complicated formatting
It was worth the waiting, Thankyou so much! It works very well
"Daniel.C" wrote: 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 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 |
All times are GMT +1. The time now is 11:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com