Form to allow user to colour fill cells of their choice

broowe17 

To achieve this functionality, you can utilize Excel's conditional formatting feature combined with some VBA (Visual Basic for Applications) code. Here is a step-by-step guide on how to implement it:

1. Set up your worksheet:

• Place your form dropdown in cell B2 with options "O.P", "C.P", and "P.S.P".
• Arrange your cells B3 to E4 for data entry.

2. Apply conditional formatting:

• Select the range B3:E4.
• Go to the "Home" tab on the Excel ribbon.
• Click on "Conditional Formatting" and then select "New Rule".
• Choose "Use a formula to determine which cells to format".
• Enter the following formula:

=INDIRECT("B2")="O.P"

• Click on the "Format" button and choose your desired fill color. Then, click "OK" to apply the formatting.

3. Repeat step 2 for each option:

• Repeat the above steps for the other options ("C.P" and "P.S.P").
• For "C.P", the formula would be =INDIRECT("B2")="C.P".
• For "P.S.P", the formula would be =INDIRECT("B2")="P.S.P".

4. Add VBA code:

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Go to Insert > Module to insert a new module.
• Copy and paste the following VBA code into the module:

Vba code is untested, please backup your file first.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range

    ' Define the range to check for changes
    Set rng = Intersect(Target, Me.Range("B2"))

    ' Check if B2 has changed
    If Not rng Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent infinite loop

        ' Loop through each cell in the range B3:E4
        For Each cell In Me.Range("B3:E4")
            ' Check if the cell is filled with a color
            If cell.Interior.ColorIndex <> xlNone Then
                ' Store the color of the cell
                If IsEmpty(cell.Value) Then
                    cell.Interior.Color = xlNone ' Clear the color if the cell is empty
                Else
                    cell.Value = cell.Value ' Retain the value of the cell
                End If
            End If
        Next cell

        Application.EnableEvents = True ' Re-enable events
    End If
End Sub

5. Save and close the VBA editor.

6. Test your worksheet:

• Now, when you select an option in cell B2 and color fill cells underneath, the color should persist when you switch between options.

This VBA code triggers whenever there's a change in cell B2. It loops through the range B3:E4, retaining the color fill for cells that have been filled while switching between options in B2. If a cell is empty, it clears the color fill but retains the value.

While using VBA provides a more dynamic solution, you can achieve a similar result using formulas alone, although it might not be as seamless. Here's a suggestion using formulas:

1. Set up your worksheet:

• Same as before, place your form dropdown in cell B2 with options "O.P", "C.P", and "P.S.P".
• Arrange your cells B3 to E4 for data entry.

2. Create a hidden area to store color information:

• In an empty area of your worksheet (e.g., starting from cell G3), create a table where each row corresponds to one of your options ("O.P", "C.P", "P.S.P").
• In the first column of the table, list your options ("O.P", "C.P", "P.S.P").
• In the second column of the table, create a named range for each option. For example, name the range for "O.P" as "Color_O.P", for "C.P" as "Color_C.P", and so on.
• In each named range, use conditional formatting formulas to store the color information based on whether the corresponding cell in B2 matches the option. For example, for "Color_O.P", the formula might be something like:

=IF($B$2="O.P", IF(B3="", "", GET.CELL(38, INDIRECT(ADDRESS(ROW(),COLUMN())))), "")

This formula checks if B2 is "O.P" and if the cell being formatted is not empty. If both conditions are met, it retrieves the color of the cell.

1. Apply conditional formatting:

• Select the range B3:E4.
• Go to the "Home" tab on the Excel ribbon.
• Click on "Conditional Formatting" and then select "New Rule".
• Choose "Use a formula to determine which cells to format".

Enter the following formula:

=IF(INDIRECT("Color_" & $B$2)=0, FALSE, TRUE)

• Click on the "Format" button and choose "Fill" to select the color stored in the corresponding named range.
• Click "OK" to apply the formatting.

1. Test your worksheet:

• Now, when you select an option in cell B2 and color fill cells underneath, the color should persist when you switch between options.

This method uses formulas and named ranges to store and apply color information based on the selected option in B2. While it's not as dynamic as the VBA solution, it achieves a similar result without requiring VBA code. Text, steps, formulas and code were created using AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.