select cell range using For loop

[goman19]

I'm trying to select a range of cells all on one line using the command

Range("C:G" & w).Select

where w is the for loop count variable. I keep getting a 1004 error so I replaced the C:G with one statement for each cell (C, D, E, F, G) which is ugly but works:

Range("C" & w).Select
...
Range("D" & w).Select
...
...
Range("G" & w).Select
...

Is there a way to modify my original code to achieve the same goal using about 30 less lines of code?

 

[ClulessChris]

Try
Range("C" & W & ":G" & W).Select

Everybody body is somebodys Nutter.

 

[goman19]

It says "Expected: list separator or )" and highlights the ":G"

Any other ideas?

 

[ClulessChris]

Could you post your code?
This works for me!

Everybody body is somebodys Nutter.

 

[goman19]

For w = 2 To totrows
Range("B" & w).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
If Range("G" & w).Value = "yes" Then
Range("C" & w).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1solid
End With
Range("D" & w).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1solid
End With
Range("E" & w).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1solid
End With
Range("F" & w).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1solid
End With
Range("G" & w).Select
With Selection.Interior
.ColorIndex = 4
.Pattern = x1solid
End With
End If
Next w

 

[SkipVought]

Hi,

This post would be better suited in VBA Visual Basic for Applications (Microsoft) forum707.

This appears to be a CONTIGUOUS range of cells (columns C thru G and rows n thru m as incrimented by w)

You could REFERENCE these cells...

Set MyRange = YourSheet.Range(Cells(n, "C"), Cells(m, "G"))

Note: under most circumstances, it is better to code using range references than using the Select Method.

Skip,

_[red][/red]

 

[SkipVought]

Use AutoFilter --

Filter on criteria "Yes"

Range reference the table

Format ONCE.

Skip,

_[red][/red]

 

[taupirho]

Try:-

range("C" & w & ":G" & w).select