I have two questions having to do with Excel VBA.

My first one is with a line like this:
Worksheets("Main page").Range("$B5")

How do I specify a a Range() of a single cell, with the column fixed (column B) but the row relative to the current row passed in to the function? ([color="#2e8b57"]Private Sub Worksheet_Change(ByVal Target As Range))

Another question is, how do I specify a single cell? Why isn't there a 'Cell' variable? Do I always have to specify a single-cell as a type 'Range'?

And my final question is, how do I do a 'reference' or 'pointer' to a variable?

For example, instead of this:
Worksheets("Main page").Range("$B5").font.Color = fontColor
Worksheets("Main page").Range("$B5").font.Size = fontSizeWorksheets("Main page").Range("$B5").font.Bold = True

I'd rather do this:
Dim myCell As <reference to> Range
myCell = Worksheets("Main page").Range("$B5")

myCell.font.Color = fontColor
myCell.font.Size = fontSize
myCell.font.Bold = True


Or, can I do this?
Dim myCell As Range
myCell = Worksheets("Main page").Range("$B5")

myCell.font.Color = fontColor
myCell.font.Size = fontSize
myCell.font.Bold = True

Worksheets("Main page").Range("$B5") = myCell


I'm completely new to Visual Basic. An hour or two of googling and frustration left me with naught. I'll pickup a VBA book sometime soon, but for now I can't get (what seems to me) to be an absurdly simple script to work.

You can use the Cells property of a Worksheet or Range to specify a particular cell. Ex: MyWorksheet.Cells(5, 3) will give you cell C5 of MyWorksheet. You can use the Row property of a given Range to get its row. AFAIK there's only the Range type, no Cell type, and I can't tell you why. If you want to set a bunch of properties of a given object all at once you can use a With block. Ex:

With Worksheets("Sheet1").Cells.Font
.Name = "Arial"
.Size = 8
End With


That's exactly what I was needing to know, thanks!