# [VBA] Excel visual basic question

This topic is 2349 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

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.

##### Share on other sites
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 

##### Share on other sites
That's exactly what I was needing to know, thanks!

• 23
• 10
• 19
• 15
• 14