I have a macro that puts data into a sheet and sets the font to bold and
color to yellow. It looks like this:

Cells(x, 7).Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6

I was reading some websites that say this is not efficient, that I should
use with:
example:

With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 6
End With

Since my code is in a loop I use Cells(x,7). How do I format the Range
object to use a variable instead of "A1"? I tried Range(Cells(x,7)) but got
an error.

Dan

Hi
For x = 1 to 10
With Cells(x,7)
.Font.bold = True
.Interior.Colorindex = 6
end with
next x

This refers to the Cells(1,7) to Cells(10,7) on the whole sheet. If you
want to loop through ten cells somewhere else e.g B3 to B13 use
With Range("B3:B13")
For x = 1 to 10
With .Cells(x,1)
.Font.bold = True
.Interior.Colorindex = 6
End With 'Cells
next x
End With 'Range

regards
Paul


Dan wrote:
I have a macro that puts data into a sheet and sets the font to bold and
color to yellow. It looks like this:

Cells(x, 7).Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6

I was reading some websites that say this is not efficient, that I should
use with:
example:

With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 6
End With

Since my code is in a loop I use Cells(x,7). How do I format the Range
object to use a variable instead of "A1"? I tried Range(Cells(x,7)) but got
an error.

Dan

Sub gsnu()
For x = 2 To 7
With Cells(x, 7)
.Interior.ColorIndex = 6
End With
Next
End Sub

You don't need to use the range function.
--
Gary's Student


"Dan" wrote:

I have a macro that puts data into a sheet and sets the font to bold and
color to yellow. It looks like this:

Cells(x, 7).Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6

I was reading some websites that say this is not efficient, that I should
use with:
example:

With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 6
End With

Since my code is in a loop I use Cells(x,7). How do I format the Range
object to use a variable instead of "A1"? I tried Range(Cells(x,7)) but got
an error.

Dan

why not:
With Cells(x, 7)
.Font.Bold = True
.Interior.ColorIndex = 6
End With


"Dan" wrote:

I have a macro that puts data into a sheet and sets the font to bold and
color to yellow. It looks like this:

Cells(x, 7).Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6

I was reading some websites that say this is not efficient, that I should
use with:
example:

With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 6
End With

Since my code is in a loop I use Cells(x,7). How do I format the Range
object to use a variable instead of "A1"? I tried Range(Cells(x,7)) but got
an error.

Dan

Dan, there is nothing magical about using Range vs using Cells, the
difference is whether or not you select the cell, so as Gary, Paul, and Jim
have shown you don't need to use the Range object, if you would like to use
Range it can be done this way:
With Range("G" & x)
.Font.Bold = True
.Interior.ColorIndex = 6
End With
--
Charles Chickering

"A good example is twice the value of good advice."


"Dan" wrote:

I have a macro that puts data into a sheet and sets the font to bold and
color to yellow. It looks like this:

Cells(x, 7).Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6

I was reading some websites that say this is not efficient, that I should
use with:
example:

With Range("A1")
.Font.Bold = True
.Interior.ColorIndex = 6
End With

Since my code is in a loop I use Cells(x,7). How do I format the Range
object to use a variable instead of "A1"? I tried Range(Cells(x,7)) but got
an error.

Dan