Auto-formatting in Excel

Question

I would like to auto-format a spreadsheet with a specific conditions. On process completion, the sheet row should be auto-formatted (i.e alternate rows have same cell background color) and the header row, usually Row 1, with different color and font bold.

NOTE: THIS NEEDS TO BE DONE BY VBA CODE.

Also note, formatting needs to be done for "n" rows which have data, leaving the rest blank.

My pagelayout Code,

Public Function SetPageLayout(pworksheet)
'Set the page layout of the worksheet to be landscape and format to fit1 page

With Sheets(pworksheet).PageSetup
    .PaperSize = xlPaperA4
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .LeftMargin = Application.CentimetersToPoints(1)
    .RightMargin = Application.CentimetersToPoints(1)
    .TopMargin = Application.CentimetersToPoints(1)
    .BottomMargin = Application.CentimetersToPoints(1)
End With

End Function

We are not your code monkey... What did you try so far? Some source code? — bash0r
Public Function SetPageLayout(pworksheet) 'Set the page layout of the worksheet to be landscape and format to fit1 page With Sheets(pworksheet).PageSetup .PaperSize = xlPaperA4 .Orientation = xlLandscape .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .LeftMargin = Application.CentimetersToPoints(1) .RightMargin = Application.CentimetersToPoints(1) .TopMargin = Application.CentimetersToPoints(1) .BottomMargin = Application.CentimetersToPoints(1) End With End Function — Manivannan KG
Bash, i am not sure what bit of code to be added for generating alternate cell BG color and thats the intent of this post. — Manivannan KG
Have you considered Conditional Formatting in the Home tab of the ribbon? Using the built-in features of Office tend to be faster than using VBA code. — Bobort

Unknown Unknown · Accepted Answer · 2014-11-26T01:23:48

Record Alt + O, A. Which gives you

 Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
    True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

Record the steps in excel macro recorder. You have to rewrite it a bit because it uses a type of syntax that vbs doesn't.

This applies (I don't have a medium9) xlRangeAutoFormatAccounting4 in vba.

Selection.AutoFormat Format:=xlRangeAutoFormatAccounting4, Number:=True, _
    Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

So first look up constants in vba's object browser. xlRangeAutoFormatAccounting4 = 17

Then look the function up in object browser and look at the bottom for the function definition,.

Function AutoFormat([Format As XlRangeAutoFormat = xlRangeAutoFormatClassic1], [Number], [Font], [Alignment], [Border], [Pattern], [Width])

So the vba becomes in vbs (and vbs works in vba) (and as you can see you can work out the correct way without needing to look the function up usually)

Selection.AutoFormat 17, True, True, True,True, True, True

So your code becomes

objXLWs.Range("A3").CurrentRegion.Select.AutoFormat 17, True, True, True,True, True, True

You are using Excel and you can record it in Excel and have Excel write your code.

Alt + T, M, R

then Home key then Up Arrow. Stop recording.

Gee look what Excel wrote

Selection.End(xlUp).Select

or if you had of recorded Go To dialog

Application.Goto Reference:="R1C1"

or if you had of recorded Ctrl + Home

Range("A1").Select

Auto-formatting in Excel

1 Answers