| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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) | |||
Linear Mode
