Formatting Excel Cells with Visual Basic

[tbg130]

Hi,

I have a number of tables that are being produced from Access data into an Excel spreadsheet. I have created multiple ranges and wish to format the ranges... My problem is that in formatting the range with BorderAround Weight:=xlMedium is that the outside of the entire range is formatted, not the individual cells within the range...

objSheet.range("Datarow" & "_" & Row & "_" & subrow).Cells.BorderAround Weight:=xlThin

How can I get the formatting to be included on every cell within a range?

Also, is there a way to attribute cells throughout the spreadsheet to a particular 'range' then have a cell that is the sum of all values in that range?

Ie. Cells as follows in 'range'...

A1, C1, E1, G1
A2, C2, E2, G2

An I attribute each cell to some 'name' or 'range' then just some all cells with same 'name' or 'range' value?

Thanks for your help in advance,

Tyler

 

[PHV]

Have you tried to replace this:
objSheet.range("Datarow" & "_" & Row & "_" & subrow).Cells.BorderAround Weight:=xlThin
By something like this ?
objSheet.Range("Datarow" & "_" & Row & "_" & subrow).Borders.LineStyle = xlContinuous

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[byundt]

The worksheet function SUM can handle a non-contiguous range

Dim rg As Range
Dim Answer As Double
Set rg=Union(Range("A1"),Range("C1"),Range("E1"),Range("G1"))
Answer=Application.Sum(rg)
Answer=Application.Sum(Range("myRange"))    'If myRange is a named range in the workbook