Need help with Range object in Macro programming

[Dan]

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

 

[paul.robinson]

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

 

[Gary''s Student]

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.

 

[Jim May]

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

 

[Charles Chickering]

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