Excel VBA, define range with a variable that has it's value gathered from a cell

This is my code:

Dim var As string
var = Range("B1").Value
Range("").Interior.ColorIndex = 5

Is it possible to define the last line like this in some way, and if so, how?:

Range("var").interior.Colorindex= 5

I want the range to be define by a value in a cell, but I am stuck here because I am not sure how to proceed. I need to be able to write individual cell numbers in cell B1 i.e A1, A2, B6 etc and use them to define the range in the code above. Thanks in advance.

2 Answers

Sure you can. The text you type into B1 just has to be one or more valid cell addresses separated by commas or a valid cell range and var is used without quotes.

Option Explicit
Sub MakeItSo() Dim var As String var = Range("B1").Value Range(var).Interior.ColorIndex = 5
End Sub

I assume you want to know how to define a name for a cell, so you can reference to it either directly from Excel, or through a VBA macro.

In order to create named cells or ranges, you make a selection in excel, then head to the formula tab, then select define name.

In the popup, it will ask you to give the cell(s) a name. In your case that would be var, but anything goes. I suggest to use something you can remember that represents this/these cells.

Once you press OK, it seems that nothing happened, but your cell(s) now got a name attached to it.

If you have linked just one cell to a name, for example B2 linked to Var, you can now do the following directly in Excel:

In B2, you enter: "test"

Now in cell B3, you enter the formula =var

In B3 the text test appears.

The same can be done in VBA.

Range("var") will link to B2 in the same way.