0

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.

8
  • No you can't since var is variable holds value/data of cell B1, if U wanna to color same cell B1 then write,, Dim rng As Range Set rng = Range("B1") rng.Interior.ColorIndex = 5 ☺ Or you may use another cell in place of B1 Feb 20, 2020 at 9:41
  • Thanks for replying 😊 I am actually trying to color the cells that I define in B1. If I manually write i.e A1, A2, G5 in B1, then I have a button that is gonna color them. Just not sure how to get it to work properly when I have to get the range value from the B1 cell🤔
    – Tom
    Feb 20, 2020 at 9:53
  • @RajeshS yes you can. You can define names for ranges, and then this will work.
    – LPChip
    Feb 20, 2020 at 10:00
  • @Tom do you want to have the VBA script define the range too, or is that something you just want to define once and be able to edit it from excel?
    – LPChip
    Feb 20, 2020 at 10:01
  • 2
    The commenters really need to go back and reread the narrative. The OP wants to type a cell reference into B1 (e.g. A1, A2, B6 etc) and have his code use that for the VBA Range reference.
    – user385793
    Feb 20, 2020 at 10:28

2 Answers 2

4

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

enter image description here

enter image description here

enter image description here

8
  • 1
    This is not what OP wants. There is a more elegant way. See my answer.
    – LPChip
    Feb 20, 2020 at 10:07
  • Not according to the narrative. ' write individual cell numbers in cell B1 i.e A1, A2, B6 etc and use them to define the range in the code'.
    – user385793
    Feb 20, 2020 at 10:09
  • @Jeeped,, your code get error 1004,, object define error ! Feb 20, 2020 at 10:20
  • @RajeshS - strange how it worked three times for me without fail. Granted the worksheet or workbook are not defined but that wasn't the question and I tried to follow the OP's code.
    – user385793
    Feb 20, 2020 at 10:22
  • 1
    @RajeshS - Yes, your code works if you keep redefining the Range object in the VBA sub but that isn't what the OP wants to do. He wants to redefine it by typing new values into B1.
    – user385793
    Feb 20, 2020 at 10:39
2

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.

3
  • 1
    var isn't a literal string in the OP's example. It is a dimmed variable holding the string from B1.
    – user385793
    Feb 20, 2020 at 10:11
  • For simple defined names given to individual cells or ranges (no formulas involved), it's probably easier to use the Name Box to the left of the formula bar.
    – user385793
    Feb 20, 2020 at 10:13
  • @Jeeped oh, I didn't know the name box actually allows you to set a name. Yeah, that's definitely easier. But you cannot actually edit the name. For editing the cell attached to the name, you do have to use the route outlined in my answer. So I'm not going to edit it.
    – LPChip
    Feb 20, 2020 at 10:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .