Declare Excel Range from Access VB variables

[Poduska]

I am trying to describe a range within an Excel worksheet where I have just copied a recordset and I always receive an error. I can create an excel address for one cell and I can format a given row, but I want to format a 2 dimentional range in the format like (a5:R28) to format all the exported data or (B5:B28) to allow me to format one column as standard, one as percentage etc.

I was thinking that that would give me all the information to create a range where I could apply a format such as font, color etc.
R = Row number with column headers, so R+1 would be first line of data (integer)
Record_Count is the lines in my Recordset which I have pasted in. (integer)
NumCol is the number of columns from my code which pastes in the column headers.(integer)

xlRangeStart and xlRangeEnd and xlRange are all declared as datatype Excel.range

xlRangeStart = "R + 1, 1"                     
xlRangeEnd = "R + 1 + Record_Count, NumCol"

Set xlRange = xlSheet.Cells(xlRangeStart, xlRangeEnd)
With xlRange
    .Font.ColorIndex = 3 'red
End With

When I run, the first line of code above gives me the error.
91 - Object variable or With block variable not set

What am I doing wrong? Is there a better way to declare a range using variables?
Thanks

 

[Remou]

If you declare xlRangeStart as Excel.range then Set is necessary:

[tt]Set xlRangeStart = xlSheet.Range("R1:C1")[/tt]

However, this will not suit the later line, so declare xlRangeStart as String or Variant. You can then have:

xlRangeStart = "R1:C1"
xlRangeEnd = "R5:C5"

Set xlRange = xlSheet.Range(xlRangeStart, xlRangeEnd)

Is this what you mean?