Hi

How can I define a dynamic range that will depend on the cell value of another sheet?

I want VBA to loop into the first sheet and identify the value of the first cell. Then go to the second sheet, identify all the cells that have the same value and define these cells as the range (cells with similar value are adjacent). Thanks

This is the section I need to change:

 
If z = Worksheets("Datab").Cells(j, 4) Then
    
RangeOfExchange = Worksheets("Datab").Cells(j, 4)
Here is the full script:

Sub Test3()

    Dim i As Integer
    Dim j as Integer
    Dim z As String
    
    Dim iLastRow As Integer 'the last row on Worksheets("Datab") column D
    Dim iLastRowSummaryC As Integer
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet    'the Worksheets("Datab")
    Dim RangeOfExchange As Range
    
        Set sh1 = ActiveWorkbook.Sheets("Summary")
        
        Set sh2 = ActiveWorkbook.Sheets("Datab")
    
        iLastRowSummaryC = sh1.[C9].End(xlDown).Row + 1
        
        iLastRow = sh2.[D2].End(xlDown).Row + 1     'the last row in Worksheets("Datab")
        
        i = 9
    
            Do While i < iLastRowSummaryC     'we loop down to the last row + 1 populated in sheet Summary, Column C (populated by the previous macro)

            z = Worksheets("Summary").Cells(i, 3)

                j = 2
            
                Do While j < iLastRow   'we loop down to the last row
            
                    If z = Worksheets("Datab").Cells(j, 4) Then
    
                    RangeOfExchange = Worksheets("Datab").Cells(j, 4)
                    
                    End If
                    
                    j = j + 1
                    
                    Loop
                    
            i = i + 1
                
            Loop

End Sub