Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to clean up code that I did through a record macro
Hello, Excel 2003 and I've recorded a macro to activate a worksheet and then
activate specific cells and format them with color, font, and alignment. The code from the macro isquite large and I know wasteful. Could someone help me with this to make it cleaner and do what I want? All of these cells are located in a Pivot table that I have already established with code. Sub ColorColumnHeaders() Sheets("Rejected Voucher Statistics").Select Columns("A:A").Select Range("A3").Activate Columns("A:A").EntireColumn.AutoFit -- autofitting the A column because it didn't do it automatically Range("A4").Select With Selection .HorizontalAlignment = xlCenter End With Range("B3").Select ActiveSheet.PivotTables("PivotTable1").PivotSelect "Team[All]", xlLabelOnly, _ True Range("B4").Select With Selection.Interior .ColorIndex = 35 -- colors the cell in B4 .Pattern = xlSolid End With With Selection .HorizontalAlignment = xlCenter ----- centers the cell in B4 End With Selection.Font.Bold = True ------Bolds the font in B4 Range("A4").Select ActiveSheet.PivotTables("PivotTable1").PivotSelect "SA[All]", xlLabelOnly, True --- don't know what this does Range("C4").Select With Selection.Interior .ColorIndex = 37 ((----- same coloring process above Column A, B and C header for the pivor table are the only ones affected)) .Pattern = xlSolid End With With Selection .HorizontalAlignment = xlCenter End With Selection.Interior.ColorIndex = 40 Range("D4").Select With Selection.Interior .ColorIndex = 40 .Pattern = xlSolid End With Range("D4").Select Selection.Interior.ColorIndex = 10 ----- colors one cell at the top of the count column ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Chrl 1'", xlDataAndLabel, _ True Selection.Font.ColorIndex = 6 End Sub Center Statistics Center Team SA Total \ Any help would be appreciated Thanks BOB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to clean up code that I did through a record macro
Hi Bob,
Try this instead: Sub ColorColumnHeaders() Set X = Range("A1, B1, C1") 'or whatever the cells you want to operate on X.Interior.ColorIndex = 3 X.HorizontalAlignment = xlCenter ' put some more code here Set X = Nothing End Sub Hope this helps, JP On Oct 5, 6:53 pm, "LabrGuy Bob R" wrote: Hello, Excel 2003 and I've recorded a macro to activate a worksheet and then activate specific cells and format them with color, font, and alignment. The code from the macro isquite large and I know wasteful. Could someone help me with this to make it cleaner and do what I want? All of these cells are located in a Pivot table that I have already established with code. Any help would be appreciated Thanks BOB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clean up code a little | Excel Discussion (Misc queries) | |||
Why doesn't Excel 2007 record charting and office art macro code? | Excel Discussion (Misc queries) | |||
help me clean up this code | Excel Programming | |||
macro code to find last record in excel | Excel Programming | |||
vba code to record a macro | Excel Programming |