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

Cells(row_num, col_num)

--

HTH

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


"sip8316" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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

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

"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

"sip8316" wrote in message
...
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