3

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Dave F
  • 109
  • 2
  • 9

1 Answers1

4

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
K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43