CODEWITHSUNDEEP
vbaexcel
Manivannan KG
Manivannan KG

Reputation: 371

Auto-formatting in Excel

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

Upvotes: 1

Views: 6669

Answers (1)

user4288955
user4288955

Reputation:

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

Upvotes: 3

Related Questions