Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Conditional Formatting

Excel Conditional Formatting

Excel Conditional Formatting

I am an excel beginner and wanted to know if it was possible to conditional format cells based on a drop down list.

For example, I have an input form that looks like this with drop down activities:

I have another sheet that has sample data formats like this (each cell has a specific custom formatted data assigned to it):

Ideally I want to conditionally format my cells to that if "walking" is selected in the 1st sheet, the data input is formatted based on the sample data format of the 2nd sheet.

Hopefully that makes sense. I played around with conditional formatting, but it seems like I need my format button to be able to be programmable with a vlookup type function.

In the end, I am going to have a whole bunch of activities in the list and I really want the input form to be dynamic based on the sample data format of the 2nd sheet.



RE: Excel Conditional Formatting

Conditional formatting applies formats to cells based on conditions. They don't dynamically create formats.

RE: Excel Conditional Formatting

3DDave, I think we are the same page here, but just to be clear when you say:

CODE --> 3DDave

Conditional formatting applies formats to cells based on conditions. They don't dynamically create formats. 

My formatting has already been created on the second sheet, I really am only trying to apply formats to cells based on conditions, its just the formatting has to be conditional as well.

In the end, I think we are on the same page, but it is just not possible with excel.


RE: Excel Conditional Formatting

Look at the rules that conditional formatting operates with.

RE: Excel Conditional Formatting

Your Format cannot be copied from anywhere. The Format must be configured in the CF Wizard.

The Formula either returns True or False. If True, the the specified Format is applied.

You can configure a Number Format like this..

Formula: =B$1="Walking"
Format>NumberFormat>Custom: General "steps"

Formula: =OR(B$1="Running", B$1="Biking", B$1="Mountain Biking")
Format>NumberFormat>Custom: General "miles"

Oh, BTW, you must SELECT the entire range you want to CF BEFORE you open the CF Wizard. Your CF Formula must be written with respect to the UPPER-LEFT cell in the CF range. Hence, a reference to B$1.

Another observation, the format of your table will restrict you in at least two ways.
1. Your CF will constantly need to be adjusted to the right as you add new activities
2. You might decide you want to add the Time of the activity at some point in the future or some other data element.

The structure of your sheet is really a report summary. I would NEVER store data in a report summary structure!

Your table ought to be something like...
Activity  ActDate  ActValue  ActUnits
Walking   8/25/20  1800      Steps
Running   8/26/20  25        Miles
...from which you could generate a PivotTable in a structure similar to your example, sans CF NumberFormat. BTW, the ActUnits CAN be a value from a Lookup, based on the value in Column A.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Conditional Formatting

The format can be applied using vba based on worksheet change events tied to the drop down cell. Create a 'cell style' and apply that via vba to a range would be the logical way to do it if you couldn't get conditional formatting to do exactly what you want. Managing a central style vs numerous conditional formats would be easier as well if you wanted to change formats or add further ones in the future.

Otherwise you can presumably do it with a lot of individual conditional formats, one for each type. Just check that your drop down cell value equals 'something' and format number and cell styles accordingly to whatever 'something' means.

If your only meaning adding the 'steps' or others afterwards and no other formatting. Why not just use a concatenated formula and helper cell/column/row to enter your raw value, and concatenate it with your desired suffix via a xlookup?

RE: Excel Conditional Formatting

Quote (IRstuff)

Your output seems to want to be using the number format options and not CF, since you can add a suffix there

You can actually change the number format via conditional formatting. Select number and custom, and entering #" steps" will achieve what is being asked I believe

RE: Excel Conditional Formatting

I appreciate the responses. I was hoping I would not have to maintain a large formula like shown by Skip (I plan to have a large amount of activities with lots of differing cell formats).

I have not worked in VBA before, I'll have to add that to my list of future learning.


RE: Excel Conditional Formatting

If you want to attempt a VBA solution, here are some caveats.

  • When your workbook is opened, Excel will ask you to Enable macros, unless you are foolish enough to carte blanche allow macros. If macros are not enabled, the VBA code will not run.
  • Some companies or individuals do not allow macros to run.
  • Macros (VBA) can error under certain conditions, often referred to as "bugs." Programmers attempt to write their code to gracefully accommodate such error conditions. Often, help that you get here and other places, tends to disregard error condition processing. After all, what you're getting is free "tips" not a rock-solid, error-proof solution that a paid professional would provide and guarantee.
  • It takes time and effort to learn VBA. It was worth it for me. I was motivated because I could see the value it would return for my career. When I encountered sites like Eng-Tips and Tek-Tips.com, I received additional help learning and applying these new skills.
And there are many more like, now you have something else to understand maintain.

But it you choose to take on these challenges, the results, meaning the things you could perform via code, open new, exciting and unimaginable horizons. However, you really need to know and understand the native capabilities of Excel before you go charging into VBA, IMNSHO.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel Conditional Formatting

That's okay, hydrae, if you really want to waste 13 minutes and 17 seconds of your life.

It has absolutely nothing to do with this thread except for the words "conditional formatting" and "Excel" and it will not enlighten you on anything of real value.

Of course, now you're gonna check it out and waste 13 minutes and 17 seconds of your life as well. O L.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close