How do I use the range property using variables

[andyjames]

At the moment I have the following code

Range("F7:H13").Select
With Selection.Interior
.ColorIndex = 44
.Pattern = xlSolid
End With


How do I replace this so that the F, 7, H and 13 are all variables that can change in the program?

 

[Norie]

Well that really depends on your variables.

Could be something like this.

With Range("F" & FirstRow & ":H" & LastRow)
     .ColorIndex = 44
     .Pattern = xlSolid
End With

 

[andyjames]

Thanks.
Unfortunately this also involves knowing the columns (namely, F and H).
May be it's not the RANGE property I should be using.
But I wanted my programme to be able to change (increment) the columns also.
Any ideas?

 

[Norie]

Well it's hard to say without seeing the rest of the code, the variables and more information on what you want to achieve.

Some ideas though - Cells, Resize, Offset.

 

[markadon]

The following code, placed in a module by itself, will do the trick. You pass in the column numbers and row numbers. You can easily adapt this to take the columns as letters ("F"-"H") rather than numbers (6'8).

THANKS TO RICHARDSCHOLLAR

Option Explicit

Sub test()
ColorCells 6, 7, 8, 13
End Sub

Function ColLet(piNum As Integer) As String
ColLet = Split(Cells(1, piNum).Address(, False), "$")(0)
End Function

Sub ColorCells(piStartCol As Integer, piStartRow As Long, _
piStopCol As Integer, piStopRow As Long)
Dim sStartLet As String
Dim sStopLet As String
sStartLet = ColLet(piStartCol)
sStopLet = ColLet(piStopCol)
Range(sStartLet & piStartRow & ":" & sStopLet & piStopRow).Select
Selection.Interior.ColorIndex = 44
Selection.Interior.Pattern = xlSolid
End Sub


--Doug