Using a variable to set a Range

[sip8316]

How do you use a variable to determine a range when changing properties in VBA?

I am setting up code for a spread sheet so that I use a loop to test a
respective cell in every row and every five or so columns. Then the way in
which that value compares to other values on the spreadsheet, determines
differnent cell properties for a different range of cells. (ie. font, font
color, fill color)

As the row and columns switch for the cell Im testing they also switch for
the Range that I want to edit the properties. The Row value is the same for
both of those but the column of the cell is different than the column values
for the Range.

I have variabes declared for both the stat column and end column of the
range but how do I set it up so that I can use variables to edit the range
properties.

I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
will do it but you can't replace the A1 with variables. I also tried
Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for an
inividual cell not a range.

Please Help,

Thanks,

Scott

[Bob Phillips]

Cells(row_num, col_num)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sip8316" <sip8316@discussions.microsoft.com> wrote in message
news:18D11FF9-BC6C-4C77-9C9B-D9BB7AB43654@microsoft.com...
> How do you use a variable to determine a range when changing properties in
VBA?
>
> I am setting up code for a spread sheet so that I use a loop to test a
> respective cell in every row and every five or so columns. Then the way
in
> which that value compares to other values on the spreadsheet, determines
> differnent cell properties for a different range of cells. (ie. font,
font
> color, fill color)
>
> As the row and columns switch for the cell Im testing they also switch for
> the Range that I want to edit the properties. The Row value is the same
for
> both of those but the column of the cell is different than the column
values
> for the Range.
>
> I have variabes declared for both the stat column and end column of the
> range but how do I set it up so that I can use variables to edit the range
> properties.
>
> I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> will do it but you can't replace the A1 with variables. I also tried
> Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for
an
> inividual cell not a range.
>
> Please Help,
>
> Thanks,
>
> Scott

[sip8316]

yeah I know how to do that, but Cells(row_num, col_num).whatever will only
edit one cell, I want to edit a range, all the cells in this range are in the
same row but take up 4-6 columns. How do I use a variable for a range?

"Bob Phillips" wrote:

> Cells(row_num, col_num)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "sip8316" <sip8316@discussions.microsoft.com> wrote in message
> news:18D11FF9-BC6C-4C77-9C9B-D9BB7AB43654@microsoft.com...
> > How do you use a variable to determine a range when changing properties in
> VBA?
> >
> > I am setting up code for a spread sheet so that I use a loop to test a
> > respective cell in every row and every five or so columns. Then the way
> in
> > which that value compares to other values on the spreadsheet, determines
> > differnent cell properties for a different range of cells. (ie. font,
> font
> > color, fill color)
> >
> > As the row and columns switch for the cell Im testing they also switch for
> > the Range that I want to edit the properties. The Row value is the same
> for
> > both of those but the column of the cell is different than the column
> values
> > for the Range.
> >
> > I have variabes declared for both the stat column and end column of the
> > range but how do I set it up so that I can use variables to edit the range
> > properties.
> >
> > I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> > will do it but you can't replace the A1 with variables. I also tried
> > Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for
> an
> > inividual cell not a range.
> >
> > Please Help,
> >
> > Thanks,
> >
> > Scott
>
>
>

[Bob Phillips]

Range(Cells(start_row_num, start_col_num),Cells(end_row_num, end_col_num))

but you probably know that already too.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"sip8316" <sip8316@discussions.microsoft.com> wrote in message
news:D7D8E372-6D17-4EB2-886F-9946116B172F@microsoft.com...
> yeah I know how to do that, but Cells(row_num, col_num).whatever will only
> edit one cell, I want to edit a range, all the cells in this range are in
the
> same row but take up 4-6 columns. How do I use a variable for a range?
>
> "Bob Phillips" wrote:
>
> > Cells(row_num, col_num)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "sip8316" <sip8316@discussions.microsoft.com> wrote in message
> > news:18D11FF9-BC6C-4C77-9C9B-D9BB7AB43654@microsoft.com...
> > > How do you use a variable to determine a range when changing
properties in
> > VBA?
> > >
> > > I am setting up code for a spread sheet so that I use a loop to test a
> > > respective cell in every row and every five or so columns. Then the
way
> > in
> > > which that value compares to other values on the spreadsheet,
determines
> > > differnent cell properties for a different range of cells. (ie. font,
> > font
> > > color, fill color)
> > >
> > > As the row and columns switch for the cell Im testing they also switch
for
> > > the Range that I want to edit the properties. The Row value is the
same
> > for
> > > both of those but the column of the cell is different than the column
> > values
> > > for the Range.
> > >
> > > I have variabes declared for both the stat column and end column of
the
> > > range but how do I set it up so that I can use variables to edit the
range
> > > properties.
> > >
> > > I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and
that
> > > will do it but you can't replace the A1 with variables. I also tried
> > > Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works
for
> > an
> > > inividual cell not a range.
> > >
> > > Please Help,
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

[Dave Peterson]

Cells(row_num, col_num).resize(newnumberofrows,newnumberofcolumns).....

is another way.

sip8316 wrote:
>
> yeah I know how to do that, but Cells(row_num, col_num).whatever will only
> edit one cell, I want to edit a range, all the cells in this range are in the
> same row but take up 4-6 columns. How do I use a variable for a range?
>
> "Bob Phillips" wrote:
>
> > Cells(row_num, col_num)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "sip8316" <sip8316@discussions.microsoft.com> wrote in message
> > news:18D11FF9-BC6C-4C77-9C9B-D9BB7AB43654@microsoft.com...
> > > How do you use a variable to determine a range when changing properties in
> > VBA?
> > >
> > > I am setting up code for a spread sheet so that I use a loop to test a
> > > respective cell in every row and every five or so columns. Then the way
> > in
> > > which that value compares to other values on the spreadsheet, determines
> > > differnent cell properties for a different range of cells. (ie. font,
> > font
> > > color, fill color)
> > >
> > > As the row and columns switch for the cell Im testing they also switch for
> > > the Range that I want to edit the properties. The Row value is the same
> > for
> > > both of those but the column of the cell is different than the column
> > values
> > > for the Range.
> > >
> > > I have variabes declared for both the stat column and end column of the
> > > range but how do I set it up so that I can use variables to edit the range
> > > properties.
> > >
> > > I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> > > will do it but you can't replace the A1 with variables. I also tried
> > > Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for
> > an
> > > inividual cell not a range.
> > >
> > > Please Help,
> > >
> > > Thanks,
> > >
> > > Scott
> >
> >
> >

--

Dave Peterson

[Toppers]

Hi,
setting a range of cells ...

Dim rng as Range
set rng=range("a1:c10")
rng.font.colorindex=3

HTH

"sip8316" wrote:

> How do you use a variable to determine a range when changing properties in VBA?
>
> I am setting up code for a spread sheet so that I use a loop to test a
> respective cell in every row and every five or so columns. Then the way in
> which that value compares to other values on the spreadsheet, determines
> differnent cell properties for a different range of cells. (ie. font, font
> color, fill color)
>
> As the row and columns switch for the cell Im testing they also switch for
> the Range that I want to edit the properties. The Row value is the same for
> both of those but the column of the cell is different than the column values
> for the Range.
>
> I have variabes declared for both the stat column and end column of the
> range but how do I set it up so that I can use variables to edit the range
> properties.
>
> I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> will do it but you can't replace the A1 with variables. I also tried
> Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for an
> inividual cell not a range.
>
> Please Help,
>
> Thanks,
>
> Scott

[sip8316]

"Toppers" wrote:

> Hi,
> setting a range of cells ...
>
> Dim rng as Range
> set rng=range("a1:c10")
> rng.font.colorindex=3
>
> HTH
>
> "sip8316" wrote:
>
> > How do you use a variable to determine a range when changing properties in VBA?
> >
> > I am setting up code for a spread sheet so that I use a loop to test a
> > respective cell in every row and every five or so columns. Then the way in
> > which that value compares to other values on the spreadsheet, determines
> > differnent cell properties for a different range of cells. (ie. font, font
> > color, fill color)
> >
> > As the row and columns switch for the cell Im testing they also switch for
> > the Range that I want to edit the properties. The Row value is the same for
> > both of those but the column of the cell is different than the column values
> > for the Range.
> >
> > I have variabes declared for both the stat column and end column of the
> > range but how do I set it up so that I can use variables to edit the range
> > properties.
> >
> > I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> > will do it but you can't replace the A1 with variables. I also tried
> > Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for an
> > inividual cell not a range.
> >
> > Please Help,
> >
> > Thanks,
> >
> > Scott

[Damien McBain]

"sip8316" <sip8316@discussions.microsoft.com> wrote in message
news:18D11FF9-BC6C-4C77-9C9B-D9BB7AB43654@microsoft.com...
> How do you use a variable to determine a range when changing properties in
> VBA?
>
> I am setting up code for a spread sheet so that I use a loop to test a
> respective cell in every row and every five or so columns. Then the way
> in
> which that value compares to other values on the spreadsheet, determines
> differnent cell properties for a different range of cells. (ie. font,
> font
> color, fill color)
>
> As the row and columns switch for the cell Im testing they also switch for
> the Range that I want to edit the properties. The Row value is the same
> for
> both of those but the column of the cell is different than the column
> values
> for the Range.
>
> I have variabes declared for both the stat column and end column of the
> range but how do I set it up so that I can use variables to edit the range
> properties.
>
> I have tried Worksheets"Sheet1".Range("A1").Font.Color Index=3, and that
> will do it but you can't replace the A1 with variables. I also tried
> Worksheets"Sheet1".Cells(x,y).Font.Color Index=3 but that only works for
> an
> inividual cell not a range.

post the code m8