Forum Discussion
Creating conditional formatting to multiple cells based on a cells contents (Creating Themes)
Hi all,
I've created a calculator. I'm trying to create a simple Themes option to easily change the look of the spreadsheet based on a cells input that uses a list type data validation entry.
How to do this?
I'm using the Desktop version of Excel in Office 365.
Edit: I also want to use formatting chosen in cells on my "data" sheet (Text color, cell fill etc)
To create conditional formatting based on a cell's contents and to switch between different themes in Excel 365, you can use a combination of data validation, conditional formatting, and the "Themes" feature. Here's a step-by-step guide:
Creating Themes:
- Prepare Themes:
- Decide on the themes you want (e.g., "Theme1," "Theme2").
- Define the formatting for each theme (font color, cell fill, etc.).
- Create a Themes Table:
- On a separate sheet, create a table that lists the themes and their corresponding formatting settings. Each row represents a theme, and each column represents a formatting option.
Data Validation for Theme Selection:
- Data Validation:
- Go to the cell where users will select the theme.
- Click on the "Data" tab.
- Choose "Data Validation."
- Set the validation criteria to "List."
- In the source box, enter the theme names (e.g., "Theme1, Theme2").
Conditional Formatting Based on Theme:
- Conditional Formatting:
- Select the range of cells you want to apply conditional formatting to.
- Go to the "Home" tab.
- Click on "Conditional Formatting" and choose "New Rule."
- Choose the rule type "Use a formula to determine which cells to format."
- Create Formulas:
- For each formatting option, create a formula that checks if the selected theme matches the theme specified in your table. For example, for font color:
=A1="Theme1"
This formula assumes the theme selection is in cell A1.
- Apply Formatting:
- Set the formatting options for each rule (font color, cell fill, etc.).
Apply Themes to Data Sheet:
- Reference the Theme Cell:
- On your "data" sheet, reference the theme cell (where users select the theme) in a cell.
=ThemeSelectionCell
- Apply Conditional Formatting on Data Sheet:
- Apply the same conditional formatting rules to your actual data based on the referenced theme cell.
Changing Themes:
- Change Theme:
- Users can change the theme by selecting a different theme from the data validation list. This will automatically update the formatting throughout the sheet.
By setting up the above structure, you can easily switch between different themes, and the conditional formatting will adapt based on the selected theme. The text, steps and functions were created with the help of AI.
My answers are voluntary and without guarantee!
Hope I was able to help you with this information.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
