Copy value of cell with given group ID to cells in different rows with same group ID

I'm trying to create a macro that constantly checks a range of cells for value changes (i.e., changes in values of dropdown lists; see example file). When a change is detected, it copies the cell's value to all cells in the same column that share a group ID with the original cell. I've cobbled something together using a simple loop (see below), but this is my first time working with VBA, and I don't get it to work. I get a runtime error 1004: "Method 'Range' of object '_Worksheet' failed" for the line setting the new cell value. Any help would be much appreciated!



Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim value As Integer, rowid As Integer, columnid As Integer
Dim lastRow As Long
Set ws = Worksheets("Sheet1")

lastRow = ws.Range("A" & Rows.Count).End(xlUp).row

If Not Intersect(Target, Range("C2:E9")) Is Nothing Then

rowid = ActiveCell.row
columnid = ActiveCell.Column

For i = 2 To lastRow
If i <> rowid Then
If ws.Range("B" & i).value = ws.Range("B" & rowid).value Then
Set Range(columnid & i).value = Range(columnid & rowid).value
End If
End If
Next

End If

End Sub

As your columnid & i has no sense, check at least the variable content …

This is what you are wanting to achieve with your code...

Please Login or Register  to view this content.

Here is a non looping option...

Please Login or Register  to view this content.

Glad to have helped...Tx for rep +
For future reference...No need to quote entire post...just clutters thread...
Can just...@ sintek...Code in Post 6 works...