Repeat existing macro for certain rows?

[Mikoustics]

Hi folks,

I have a macro to update cells C18 and D18 to be blank when a choice in a data validation list in B18 is changed (refreshing an INDIRECT list in cell C18 related to a B18 data validation list to blank and to a blank cell in D18). I would like this macro to be repeated for the whole cell ranges C18:C37 and D18:D37 when its adjacent choice cell (B18:B37) is changed.

e.g. If the user chooses an option from the data validation list in cell B21, I would like only cells C21 and D21 to refresh to blank.

Cheers for any help! Here is the code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngParentCell As Range
Dim rngDepCell As Range
Dim rngCell As Range

Set rngParentCell = Range("B18")
Set rngDepCell = Intersect(Target, rngParentCell)

If Not rngDepCell Is Nothing Then
Set rngCell = Range("C18:D18")
rngCell.ClearContents
rngCell.Select
MsgBox _
Title:="Notice", _
Prompt:="Please select 'Type', 'Length of Duct (m) and 'No. (Default 1)'", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

Set rngParentCell = Nothing
Set rngDepCell = Nothing
Set rngCell = Nothing

End Sub

 

[syntaxed]

You could try changing:

Set rngParentCell = Range("B18")

to:

Set rngParentCell = Range("B18:B37")

And:

Set rngCell = Range("C18:D18")

to:

Set rngCell = Range("C" & rngDepCell.Row & ":D" & rngDepCell.Row)

However, if more than one cells in the target range changes at the same time, e.g. B18 and B19 both change at the same time then the above code will only blank out cells for the first row, i.e. C18 and D18.

But I'm assuming that this wouldn't be a problem because you're using data validation lists so only one cell is expected to change at a time.

 

[Mikoustics]

Thanks very much for your help syntaxed! It works perfectly.