# Excel - Increasing alphabectically

Asked By HarryGuy on 27-Nov-09 10:14 AM
```Windows XP and Excel 2003

The first time through the program should read:

With Active Sheet
.First = Sheets("Rates").Range("B2")
.Second = Sheets("Rates").Range("B3:B186")
End With

The next time through the Ranges should read "C2" and "C3:C186"

Then next time "D2" and "D3:D186" and so on until it reaches "X2"and

Can someone suggest a good way to do this. Please.```

Bernard Liengme replied to HarryGuy on 27-Nov-09 10:45 AM
```You could use index values rather than the A1 convention
In the sub below, first the values in B2:E2 are displayed,
then the ranges B3:B5, C3:C5, D3:D5 and E3:E5 are given a colour fill

Sub tryme()
For J = 2 To 5
MsgBox Worksheets("Sheet1").Cells(2, J)
Set myrange = Worksheets("Sheet1").Range(Cells(3, J), Cells(5, J))
myrange.Interior.ColorIndex = J + 5
Next J
End Sub

hope this helps
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP```
Barb Reinhardt replied to HarryGuy on 27-Nov-09 11:11 AM
```I will give you what you need to answer your question, however some of your
syntax does not make sense.  If you  have other questions, come back.

Option Explicit

Sub test()

Dim myRange As Excel.Range
Dim r As Excel.Range

Dim myWS As Excel.Worksheet

Set myWS = Worksheets("Rates")
Set myRange = myWS.Range("B2:X2")

For Each r In myRange
'fill in what you want to do here.
Next r

End Sub
--
HTH,

Barb Reinhardt```
Mike H replied to HarryGuy on 27-Nov-09 11:28 AM
```Hi,

you could get into a mess using second ,it is a reserved word and with out
seeing your code it is hard to be much help but maybe this

For col = 2 To 25
first = Sheets("Rates").Cells(2, col)
sec = Range(Cells(3, col), Cells(186, col))
next

Mike```
HarryGuy replied to Barb Reinhardt on 28-Nov-09 09:53 AM
`Thanks very much to all who responded - very much appreciated.`