Home 
Search 
Today's Posts 
#1




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 
#2




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 
#3




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
a little complicated  Excel Worksheet Functions  
Too Complicated For Me  Excel Discussion (Misc queries)  
Something perhaps a little complicated  Excel Discussion (Misc queries)  
Complicated conditional formatting if numbers match across a data set..How?  Excel Worksheet Functions  
Complicated conditional formatting if numbers match across a data set..How?  Excel Discussion (Misc queries) 