Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting & colours
First for the headers, your code can be reduced by quite a
significant amount by trying this (you mentioned center alignment, but didn't have it coded): With ThisWorkbook.Worksheets(1).Range("A1:F1") .Value = Array("Company Name", "Company Name", _ "Address", "Invoice Date", "Invoice Number", "Amount") With .Font .Name = "Arial" .Size = 12 .Bold = True End With .HorizontalAlignment = xlCenter End With For the total row, something similar With ThisWorkbook.Worksheets(1).Range("A" & iRow & ":F" & iRow) ..Font .Name = "Arial" .Size = 12 .Bold = True .HorizontalAlignment = xlCenter End With -----Original Message----- Hi there, i have posted my code below for reference. my problem is i want the titles of my colums to be bolded, centred and have a font size of 12, then the total at the bottom to also be bolded, centred & have a font size of 12 (with a $ sign in there as well) i thought i had done that (well i did, but it ran from a 2nd macro, and i wanted it to run all form the 1 macro, so i pasted the code inside the 1st macro. the macro runs but it doesn't give me the desired result. any assistance is greatly appreciated. Regards, Scott Sub GetMyData() Application.ScreenUpdating = False Dim objFSO As Scripting.FileSystemObject Dim objFolder As Scripting.Folder Dim objSubfolder As Scripting.Folder Dim objFile As Scripting.File Dim iRow As Long iRow = 3 ThisWorkbook.Worksheets(1).Cells(iRow - 2, 1) = "Company Name" ThisWorkbook.Worksheets(1).Cells(iRow - 2, 2) = "Company Name" ThisWorkbook.Worksheets(1).Cells(iRow - 2, 3) = "Address" ThisWorkbook.Worksheets(1).Cells(iRow - 2, 4) = "Invoice Date" ThisWorkbook.Worksheets(1).Cells(iRow - 2, 5) = "Invoice Number" ThisWorkbook.Worksheets(1).Cells(iRow - 2, 6) = "Amount" Set objFSO = CreateObject ("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder ("e:\scott\Sotek\Invoices\") For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name With ActiveWorkbook.Worksheets(1) ThisWorkbook.Worksheets(1).Cells(iRow, 1).Value = ..Range("A13").Value ThisWorkbook.Worksheets(1).Cells(iRow, 2).Value = ..Range("A14").Value ThisWorkbook.Worksheets(1).Cells(iRow, 3).Value = ..Range("A14").Value ThisWorkbook.Worksheets(1).Cells(iRow, 4).Value = ..Range("F7").Value ThisWorkbook.Worksheets(1).Cells(iRow, 5).Value = ..Range("F8").Value ThisWorkbook.Worksheets(1).Cells(iRow, 6).Value = ..Range("F45").Value End With ActiveWorkbook.Close savechanges:=False iRow = iRow + 1 End If Next ThisWorkbook.Worksheets(1).Cells(iRow + 1, 6) = "=Sum (F2:F" & (iRow - 1) & ")" ThisWorkbook.Worksheets(1).Cells(iRow + 1, 5) = "TOTAL" ThisWorkbook.Worksheets(1).Cells.EntireColumn.Auto Fit ThisWorkbook.Worksheets(1).Range("E68:F68").Select ThisWorkbook.Worksheets(1).Range("F68").Activate With Selection.Font .Name = "Arial" .Size = 12 .Bold = True End With ThisWorkbook.Worksheets(1).Range("F68").Select Selection.Style = "Currency" Application.ScreenUpdating = True End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printer not picking up colours from conditional formatting. | Excel Discussion (Misc queries) | |||
Conditional Formatting / Cell Fill Colours | New Users to Excel | |||
how do i get more than 3 colours in conditional formatting | Excel Discussion (Misc queries) | |||
colours missing in conditional formatting. | Excel Worksheet Functions | |||
Conditional Formatting with Colours / Colors | Excel Worksheet Functions |