Formating and Excel Worksheet

[Kenln]

I need Borders around data in a sheet that I populate. The columns are letters causing me to duplicate the code. Instead of Column E, G, I, it would be nice to use a For Next statment.

Any idea?

    strStartCell = "E" + CStr(intRowStart)
    strCellRange = strStartCell + "..." + strEndColumn + CStr(intRowNum)
    With XlSheet.Range(strCellRange).Borders(xlEdgeLeft)
        .Weight = xlThick
    End With
    strStartCell = "G" + CStr(intRowStart)
    strCellRange = strStartCell + "..." + strEndColumn + CStr(intRowNum)
    With XlSheet.Range(strCellRange).Borders(xlEdgeLeft)
        .Weight = xlThick
    End With
    strStartCell = "I" + CStr(intRowStart)
    strCellRange = strStartCell + "..." + strEndColumn + CStr(intRowNum)
    With XlSheet.Range(strCellRange).Borders(xlEdgeLeft)
        .Weight = xlThick
    End With

 

[Matt Greatorex]

I need to produce a lot of Access reports as Excel spreadsheets, as the company I'm working at likes to play around with formatting. The easiest way i've found of doing any of the formatting stuff is to

1) Open a blank sheet in Excel
2) Start recording a macro
3) Carry out whatever steps you want
4) Stop recording the macro
5) Step into the macro to see the code it produces automatically.

In most cases, the code can be copied and pasted as is.
In the others, it at least usually identifies exactly what to do, so can be adapted to suit whatever you need.

For example, I forget the exact syntax, but I know that when you're doing something to a range of cells (as you describe), you can use something like the following to justify the text in all columns A to E and all rows from 1 down to whatever value CellRef is.

XLSheet.Range("A1:E" & CellRef & "").HorizontalAlignment = xlCenter

Hope that helps.

 

[Kenln]

That works, I was trying to avoid having to dulicate the code.

thanks