Selecting object range of a different sheet VBA

[jamiex90]

I apologize if I didn't state the subject accurately.

I have numbers scattered across a spreadsheet. I have code to get the ranges of all those cells of a specific value into one range object via the union function. I want to select those same ranges but on a different worksheet. This is so I can have a color map of all the values but on a different sheet. Thank you very much for your time.

Dim cell As Range
Dim myRng As Range
 
 For Each cell In ActiveSheet.Range("A1:M56")
 If cell.Value = 193 Then
     If Not myRng Is Nothing Then
        Set myRng = Union(myRng, cell)
     Else
        Set myRng = cell
     End If
 End If
 Next cell
 
'alt 1----------------------------
 myRng.select 'This selects all the cells with the value 193 but I want to select those ranges on the "GRID" worksheet, not the active one with all the values in it.

'alt 2----------------------------
 Sheets("GRID").Select
 myRng.select 'This doesn't work. I assume it's because myRng is storing the original sheet in it. Is there a way I can just get the range without the parent?

'alt 3----------------------------
 Sheets("GRID").Range(myRng.Address).Select 'This works like I generally want it to, but I hit a character limit of 253 and the selection doesn't include the full range when I have a larger dataset.

'I printed myRng.Address and got:
'--"$A$1:$M$3,$B$4:$M$4,$C$5:$M$5,$D$6:$M$6,$E$7:$M$7,$F$8:$M$8,$G$9:$M$9,$H$10:$M$10,$I$11:$M$11,$J$12:$M$12,$K$13:$M$13,$L$14:$M$14,$M$15,$L$16,$K$15:$K$17,$M$17,$J$14:$J$18,$L$18:$M$18,$E$9:$E$19,$I$13:$I$19,$K$19:$M$19,$D$8:$D$20,$F$10:$F$20,$G$11:$G$20"

'I am limited to 253 as my character limit for holding a range address. For my purposes I need that to be able to hold far much more than that.

'I've tried breaking that long address into an array with the comma as a delimiter but that was getting too convoluted. I feel like I am missing something simple.

End Sub

 

[Domenic]

I don't understand what it is you're trying to do, but let's say that you want to color the corresponding cells in your GRID worksheet, you can simply do as follows...

Dim cell As Range

For Each cell In ActiveSheet.Range("A1:M56")
    If cell.Value = 193 Then
        Worksheets("GRID").Range(cell.Address).Interior.Color = RGB(255, 0, 0) 'red
    End If
Next cell

Hope this helps!

 

[juddaaaa]

Another option

Sub MrExcel()

    Dim cell As Range
    Dim Rng As Variant
    Dim myRng_1 As Range
    Dim myRng_2 As Range
    
    For Each cell In ActiveSheet.Range("A1:M56")
        If cell.Value = 193 Then
            If Not myRng_1 Is Nothing Then
                Set myRng_1 = Union(myRng_1, cell)
            Else
                Set myRng_1 = cell
            End If
        End If
    Next cell
    
    Sheets("GRID").Select
    
    For Each Rng In Split(myRng_1.Address(0, 0), ",")
        If Not myRng_2 Is Nothing Then
            Set myRng_2 = Union(myRng_2, ActiveSheet.Range(Rng))
        Else
            Set myRng_2 = ActiveSheet.Range(Rng)
        End If
    Next Rng
    
    myRng_2.Select

End Sub

 

[jamiex90]

I realize now that I have been going down the wrong path. I'll be going with your solution. Thank you very much!

 

[jamiex90]

Thanks, Juddaaaa. That gives me closure as I spent a lot of time trying to select those cells. Though, I should have just bypassed the selection and went right to coloring as seen in Dominic's post.

 

[Domenic]

I realize now that I have been going down the wrong path. I'll be going with your solution. Thank you very much!

You're very welcome, cheers!