How to perform a similar action on multiple cells with a varying parameter

Question

I was asked, after providing this answer, how to make the following code work in the case where you have multiple cells (ie. TARGET_CELL_ADDRESS should take multiple values) and each cell has their own parameter (PLACEHOLDER_TEXT in this case).

Option Explicit

Const TARGET_CELL_ADDRESS As String = "C2"
Const PLACEHOLDER_TEXT As String = "My placeholder text"

Const GREY_COLOR = 10921637
Const BLACK_COLOR = 0

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Replace(Target.AddressLocal, "$", "") = TARGET_CELL_ADDRESS Then
        'Clear text content
        With Target
            If .Value2 = PLACEHOLDER_TEXT Then
                .Value2 = ""
                .Font.Color = BLACK_COLOR
            End If
        End With
    Else
        'Restore placeholder text if needed
        Dim Rng As Range
        Set Rng = Me.Range(TARGET_CELL_ADDRESS)
        With Rng
            If .Value2 = vbNullString Then
                .Value2 = PLACEHOLDER_TEXT
                .Font.Color = GREY_COLOR
            ElseIf .Value2 = PLACEHOLDER_TEXT Then
                If .Font.Color <> GREY_COLOR Then
                    .Font.Color = GREY_COLOR
                End If
            End If
        End With
    End If
        
End Sub

I thought it would be better to answer in a seperate question and try to make the title of the question more general. Hopefully, it will make it easier to find the answer to this kind of question.

Answer 1

In this case, we need a data structure that will allow us to loop over the different cells and retrieve the corresponding text for each cell.

A good choice for this is a Scripting Dictionary since it will allow us to store the address of the cell as the key and the text as the value (aka. item).

Note that :

To use the Dictionary (with early binding) you need to first add the reference:

Go to Tools->References from the Visual Basic menu. Find "Microsoft Scripting Runtime" in the list and place a check in the box beside it.

Read more on how to use Dictionaries here.

Once we have our dictionary, we will be able to wrap the code that need to run for multiple cells around a For Each loop. That will allow to re-use the code with each key (cell address) replacing the TARGET_CELL_ADDRESS constant and the corresponding item to replacing the PLACEHOLDER_TEXT constant.

Option Explicit

Const GREY_COLOR = 10921637
Const BLACK_COLOR = 0

'Let's define the dictionary at the worksheet module level, so we can store its values in between each run of Worksheet_SelectionChange
Private CellsDict As Dictionary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Create the dictionary to store the information (if it doesn't already exists)
    If CellsDict Is Nothing Then
        Set CellsDict = New Dictionary
        CellsDict.Add "C9", "Text for C9"
        CellsDict.Add "C21", "Text for C21"
        CellsDict.Add "C58", "Text for C58"
        CellsDict.Add "C96", "Text for C96"
    End If

    'Now let's loop over each key inside the dict to perform the same changes/checks on each cells listed inside of it.
    Dim Key As Variant
    For Each Key In CellsDict.Keys

        ''''''''''''''''''''''''''
        'Here goes the code that needs to run for each cell address in the dictionary
        ''''''''''''''''''''''''''

        If Replace(Target.AddressLocal, "$", "") = Key Then
            'Clear text content
            With Target
                If .Value2 = CellsDict.Item(Key) Then
                    .Value2 = ""
                    .Font.Color = BLACK_COLOR
                End If
            End With
        Else
            'Restore placeholder text if needed
            Dim Rng As Range
            Set Rng = Me.Range(Key)
            With Rng
                If .Value2 = vbNullString Then
                    .Value2 = CellsDict.Item(Key)
                    .Font.Color = GREY_COLOR
                ElseIf .Value2 = CellsDict.Item(Key) Then
                    If .Font.Color <> GREY_COLOR Then
                        .Font.Color = GREY_COLOR
                    End If
                End If
            End With
        End If

        ''''''''''''''''''''''''''

    Next

End Sub

Note: This code should work well even for a dozen of cells, but would need to be optimized in case the list of cells is a few orders of magnitude bigger.