Excel Conditional formatting

Copper Contributor

Hi All,

I need help with the conditional formatting based on the drop down values selected. If a particular drop down value is selected it should pop out the respective columns in green and others should be grayed out. Selecting the drop down value is dynamic and it should be highlighted accordingly. I did an alternate solution with VBA but it is not approved. Please provide solution other than VBA. Thanks everyone!

Below is how my excel sheet looks. 



Below is how I want it!



2 Replies

Hi  @Mkamala1994,

you can achieve the desired formatting based on the selected dropdown values, you can try to use conditional formatting without the need for VBA:

  1. Select the range of cells or columns that you want to format.

  2. Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.

  3. Choose "New Rule" from the options.

  4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."

  5. Enter the following formula in the provided field:


    Make sure to replace "YourDropdownValue" with the specific value that, when selected in your dropdown, should trigger the formatting.

  6. Click on the "Format" button to define the formatting style you want to apply. In the "Format Cells" dialog box, navigate to the "Fill" tab, choose the green color you prefer, and click "OK."

  7. Confirm the settings by clicking "OK" in the "New Formatting Rule" dialog box.

  8. Repeat these steps for each dropdown value you want to format differently, adjusting the formula and formatting options accordingly.

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.

    If the post was useful in other ways, please consider giving it Like.

    Kindest regards,

    Leon Pavesic

Hi. Sorry if my understanding is wrong. No steps to input the formula or function to highlight the column values and the others to be grayed out. So does it still work in the way which I expect. Thanks!