Apply Dynamic Formatting to your Dropdown List in Excel

Are you tired of wrestling with Excel's conditional formatting? Imagine having dropdown lists that automatically style your tables! Recently, a client of mine wanted to use dropdown lists but also apply specific formatting based on the values selected. To make this flexible, I created a dynamic solution that allows him to add extra items and change the formatting as needed without having to call me every time.

Here’s how you can do it too!

First, Create Your Dropdown List and Legend:

• Create a table in Excel (e.g. named TblPriorities). This table can be in a separate sheet and in our example is called "Settings".

• In the first column list your data items for the drop down lists.

• Name this range for data validation reference e.g. "ListPriority"

• In the second column set the corresponding formats.
  

Now, let’s set up the dropdown lists in your main table:

• Highlight the cells where you want the dropdowns to appear. For example in a table (named TblProjects) on a sheet named "Projects"

• Go to the Data tab (from Excel ribbon) and click Data Validation.

• Choose List from the Allow dropdown.

• Set source to be the list you named above e.g. using formula =INDIRECT("ListPriority") and click OK.
  

Create VBA Code for Dynamic Formatting

• Press Alt + F11 to open the VBA editor.

• Insert a new module and paste the following code:

Create a Button to Run the Macro

Here is where the magic will happen for the user. Create a button to run the macro. You can do this by creating a form control in the Developer Tab, but I like to use Shapes because then I can make them look nicer and how I want more easily.

1. Insert a Shape:

• Go to the Insert tab.

• Click on Shapes and choose a shape (e.g., a rectangle or a rounded rectangle).

• Draw the shape on your sheet where you want the button to be.
  

1. Add Text to the Shape:

• Right-click the shape and select Edit Text.

• Enter a descriptive text like "Apply Formatting".
  

1. Assign the Macro to the Shape:

• Right-click the shape and select Assign Macro.

• Choose ApplyConditionalFormatting from the list and click OK.
  

Populate Dropdown Lists

• Go to your table and populate your column using the dropdown lists. Ensure that all cells with dropdown lists have a value selected. At first you won't see the formatting - that's okay... it's coming!

Click the Button to apply dynamic formatting to your dropdown list in Excel!

• Simply click the "Apply Formatting" button to run the macro.

• Watch as your table updates with the specified formats! It's Magic!

With dynamic dropdowns, automatic formatting, and a convenient button, your Excel sheets can now be as flexible and user-friendly as you need. This solution allows you to add items and change the formatting effortlessly without needing to dive into complex conditional formatting each time.

Try changing or adding items to your lists and apply different formatting!

• Note when changing your list for the data validation you will have to reselect items on your main table.

• Changing items in the settings table does not automatically change the items in your projects table.

• Give it a try and have fun!
  

What are your thoughts on this Excel magic? Have any comments or specific things you'd like to learn about Excel? Or do you have cool ideas to share with us? Share your ideas in the comments!