Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Row with auto formatting.
I have a formula here that automatically inserst a new row and formats the
text in it bold. I would like this newly inserted row to automatically be formatted to have the cells AC:AF formatted with a thin line boarder and fill color Aqua. Can anyone help with the code please Thank you in advance Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "TotalHours" Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Row with auto formatting.
hi
add this at the end of your code for the color and line. i am not sure what your definition of "Aqua" is so i guessed. Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 28 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin see this site for other colors. http://www.mvps.org/dmcritchie/excel/colors.htm regards FSt1 "aussiegirlone" wrote: I have a formula here that automatically inserst a new row and formats the text in it bold. I would like this newly inserted row to automatically be formatted to have the cells AC:AF formatted with a thin line boarder and fill color Aqua. Can anyone help with the code please Thank you in advance Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "TotalHours" Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Row with auto formatting.
The colorIndex was 42 and yes this works beautiful. Thank you
"FSt1" wrote: hi add this at the end of your code for the color and line. i am not sure what your definition of "Aqua" is so i guessed. Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 28 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin see this site for other colors. http://www.mvps.org/dmcritchie/excel/colors.htm regards FSt1 "aussiegirlone" wrote: I have a formula here that automatically inserst a new row and formats the text in it bold. I would like this newly inserted row to automatically be formatted to have the cells AC:AF formatted with a thin line boarder and fill color Aqua. Can anyone help with the code please Thank you in advance Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "TotalHours" Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert New Row with auto formatting.
glad to help
regards FSt1 "aussiegirlone" wrote: The colorIndex was 42 and yes this works beautiful. Thank you "FSt1" wrote: hi add this at the end of your code for the color and line. i am not sure what your definition of "Aqua" is so i guessed. Union(Cells(n, "AF"), Cells(n, "AC")).Interior.ColorIndex = 28 Union(Cells(n, "AF"), Cells(n, "AC")).Borders.LineStyle = xlContinuous Union(Cells(n, "AF"), Cells(n, "AC")).Borders.Weight = xlThin see this site for other colors. http://www.mvps.org/dmcritchie/excel/colors.htm regards FSt1 "aussiegirlone" wrote: I have a formula here that automatically inserst a new row and formats the text in it bold. I would like this newly inserted row to automatically be formatted to have the cells AC:AF formatted with a thin line boarder and fill color Aqua. Can anyone help with the code please Thank you in advance Sub NewRow() EndRow = Cells(Rows.Count, 1).End(xlUp).Row n = Cells(Rows.Count, "AF").End(xlUp).Row + 1 Cells(n, "AC").Value = "TotalHours" Cells(n, "AF").Formula = "=sum(AF1:AF" & n - 1 & ")" Union(Cells(n, "AF"), Cells(n, "AC")).Font.Bold = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Insert NEw Row with formatting | Excel Discussion (Misc queries) | |||
Need VBA script to auto-insert value upon row insert | Excel Worksheet Functions | |||
auto insert | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
Auto Insert/Auto Formula? | Excel Worksheet Functions |