Apr 29 2024 09:09 PM
I would like to include a feature in my spreadsheet. In cell B2 I have a list form, showing the options "O.P", "C.P" and "P.S.P". Underneath in cells B3 to E3 are two rows of the following options "Socials", "Alert", "Going" and "Subscription". Photo below for example.
I have already formatted each of the cells from B3 to E4 to display their respective text, dependent on the value in B2. So when either of the options are selected in the form in B2, the cells underneath will display the same word text. For example, the formula I have used in cell B4 is this "=IF(B2="O.P","Socials",IF(B2="C.P","Socials",IF(B2="P.S.P","Socials",""))) ".
Everything works fine when switching between the forms. What I need to include is a feature where the user can choose any of the options in B2 and colour fill in the cells underneath as they need.
Right now when I do this, the colour filled cells stay the same across all of the options in B2. What I need is for the colour filled cells to stay the same, respective to the option in B2.
Ultimately, if the user is on the "O.P" option in B2, and they colour fill cells B3 and D3, then go to the "C.P" option in B2 and colour fill cells C3 and D3, when they go back to the "O.P" option, cells B3 and D3 will still be colour filled, and when they go back to the "C.P" option, cells C3 and D3 will still be colour filled.
How would I be able to include this feature in my spreadsheet please?
Apr 30 2024 02:03 AM
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:
2. Apply conditional formatting:
=INDIRECT("B2")="O.P"
3. Repeat step 2 for each option:
4. Add VBA code:
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:
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:
2. Create a hidden area to store color information:
=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.
Enter the following formula:
=IF(INDIRECT("Color_" & $B$2)=0, FALSE, TRUE)
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.