using the code for different set of ranges

Question

Situation Hello SO, I have a situation where, I have the following code which I would like to use over other ranges to. But in this case, when the case ranges changes, the clear contents and otehr defined ranges will also change. I find that I have to write a repeatative code for other ranges. Is it possible that I could possibly use another way to define my range and use this code for other range as well. To explain properly, I am finding it difficult but my aim is to optimize the program where I could do by not writing the code again and again but only change of ranges could be porrible.

Sub DescriptionisActivated()
Select Case Range("A12")
Case Is = ""
        If Sheet3.Range("B16") <> "" Then
        MsgBox "Input1" & vbNewLine & vbNewLine & "-Imput2", vbExclamation, " missing"
        Sheet3.Range("B16:L16").ClearContents: Sheet3.Range("A12").Select
        End If
Case Is <> ""
        If Sheet3.Range("B16") <> "" Then
         Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("B16").Offset(0, 2).Select
        Else
        Sheet3.Range("N16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("P16").Interior.Color = RGB(255, 255, 0): Sheet3.Range("N16,P16:S16").ClearContents: Sheet3.Range("B16").Select
        End If
End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$16" Then
    Sheet3.Unprotect ""
    Call DescriptionisActivated
    Sheet3.Protect ""
End If

Answer 1

so if you change

Sub DescriptionisActivated()

to

Sub DescriptionisActivated(Target As Range)

and send it a range when you call it i.e.

Call DescriptionisActivated(Target)

then it has a range to start with.

Once that is done you need to change every range refence in DescriptionisActivated to rely on the Target Range. So for example replace:

Range("P16")

with

Target.Offset(0, 14)

be careful as you do this to make ensure that they will point to the right cells that are required for each possible Target you would send to it

let me know how it goes!