Prev: Save data before printing
Next: Code not working in a PROTECTED Worksheet
From: Bean Counter on 7 Jun 2010 15:11
Hi All,

I want to select a range of cells and format the range of cells based on a
"offset" feature...

The last couple lines in my code are:

ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Where the active cell reference is a moving target depending on the amount
of data in the worksheet. How do I now say - select this cell, plus other
cells in this range of cells and format them with a border, and a color? I
can't get the syntax right. I I record the macro, it only gives me this:
range("I32:L37").Select -which is not what I want....

Thanks for the help..

--
Thanks for all of the help. It is much appreciated!!!!
From: GS on 7 Jun 2010 15:36
Bean Counter has brought this to us :
> Hi All,
>
> I want to select a range of cells and format the range of cells based on a
> "offset" feature...
>
> The last couple lines in my code are:
>
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
>
> Where the active cell reference is a moving target depending on the amount
> of data in the worksheet. How do I now say - select this cell, plus other
> cells in this range of cells and format them with a border, and a color? I
> can't get the syntax right. I I record the macro, it only gives me this:
> range("I32:L37").Select -which is not what I want....
>
> Thanks for the help..

This is what I got when I ran the macro recorder:

Sub Macro2()
Range("I32:L57").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


From: Don Guillett on 7 Jun 2010 16:50
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Bean Counter" <BeanCounter(a)discussions.microsoft.com> wrote in message
news:98B11A0F-D7F8-42E7-8E54-63587286B7BC(a)microsoft.com...
> Hi All,
>
> I want to select a range of cells and format the range of cells based on a
> "offset" feature...
>
> The last couple lines in my code are:
>
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
>
> Where the active cell reference is a moving target depending on the amount
> of data in the worksheet. How do I now say - select this cell, plus other
> cells in this range of cells and format them with a border, and a color?
> I
> can't get the syntax right. I I record the macro, it only gives me this:
> range("I32:L37").Select -which is not what I want....
>
> Thanks for the help..
>
> --
> Thanks for all of the help. It is much appreciated!!!!

 | 
Pages: 1
Prev: Save data before printing
Next: Code not working in a PROTECTED Worksheet