Query rows from a named range in active sheet as an array

Hi chickpeazy,

Not sure if this is exactly what you need but it is a starting point... it will load an array with the row numbers of the named range, just change the sheet name, and the name of the named range.

Sub chickpeazy()

Dim myRow As Range
Dim myArray() As Integer
Dim i As Long

For Each myRow In Worksheets("Sheet1").Range("NamedRange").Rows
    ReDim Preserve myArray(i)
    myArray(i) = myRow.Row
    i = i + 1
Next myRow

End Sub

Thank you for that! I think you understand what I am trying to do, but implementing it in code that another user here helped make does not do anything for some reason.
Also, thinking about this... an easier way to accomplish what I am trying to do might be to change the color of all cells in the named range because the named range just consists of rows anyway?

PHP Code: 

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim varMyRows As Variant
    Dim varMyRow  As Variant
    Dim colFilNorm As Long
    Dim colHighlight As Long
    Dim rowsForArray As Long
    Dim myRow As Range
    Dim myArray() As Integer
    Dim i As Long

    For Each myRow In Worksheets("Sheet1").Range("corValTest").Rows
        ReDim Preserve myArray(i)
        myArray(i) = myRow.Row
        i = i + 1
    Next myRow
    
    colFilNorm = ActiveCell.Interior.Color
    colHighlight = RGB(255, 255, 0)
    
    varMyRows = Array(myArray) '<- Row(s) to be coloured upon cell B2 being activated
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    For Each varMyRow In varMyRows
        Rows(varMyRow).Interior.Color = ActiveCell.Interior.Color
    Next varMyRow
    
    If Target.Address = "$B$2" Then
        For Each varMyRow In varMyRows
            Rows(varMyRow).Interior.Color = colHighlight
        Next varMyRow
    End If
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub 


Why do you have this, myArray is already an array?

  varMyRows = Array(myArray) '<- Row(s) to be coloured upon cell B2 being activated

Also, if you wanted to format the range you wouldn't need an array or a loop.

Worksheets("Sheet1").Range("corValTest").Interior.Color = ActiveCell.Interior.Color

Ah, yes that command seems to be much more succinct... thank you! Though this code still doesnt do what I want (to highlight the named range cells when "B2" is selected and unhighlight otherwise)

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim colFilNorm As Long
    Dim colHighlight As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
      
    colFilNorm = ActiveCell.Interior.Color
    colHighlight = RGB(255, 255, 0)
    
    Worksheets("Sheet1").Range("corValTest").Interior.Color = colFilNorm
    
    If Target.Address = "$B$2" Then
    Worksheets("Sheet1").Range("corValTest").Interior.Color = colHighlight
    End If
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub