Reference Named Ranges in VBA

Question

In my Excel workbook, I've made a single cell a named range. Instead of using vba to do this, I just went to Formulas > Name_Manager > New in Excel. I now want to refer to this named range in a macro that I'm writing. The macro will copy the color of a cell if that cell's background color matches that of my named range.

My macro currently works well when I refer to the cell's location as Range("S2") in the following code:

Trans_ECO_Row.Cells(, 13).Value = Trans_Queue_Row.Cells(, 14).Value
    If Trans_Queue_Row.Cells(, 14).Interior.Color = QueueSheet.Range("S2").Interior.Color Then
        Trans_ECO_Row.Cells(, 13).Interior.Color = Trans_Queue_Row.Cells(, 14).Interior.Color
    End If

However, I've named cell S2 MGRColor by going to Formulas > Name_Manager > New in Excel. How can I replace S2 with MGRColor in my above code?

K.Dᴀᴠɪs · Accepted Answer · 2019-07-16 22:20:50Z

5

You can do this using the Range() function. The named range would be the function's only argument, surrounded by double quotes:

Range("MGRColor")

You can use this as you would any other range object:

Range("MGRColor").Value = ...
Range("MGRColor").Interior.Color = ...
' Etc

edited Jul 16, 2019 at 22:20

answered Jul 16, 2019 at 22:15

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reference Named Ranges in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related