Thread: Conditional formatting multiple criteria

Hello. I'm hoping someone can help me with this challenge. I'll try and explain what I need and how the data is set up below, but let me know if more information is needed.

I'm trying to set up some conditional formatting and I'm not sure what code to enter into VBA to get what I'm trying to accomplish.

At it's basic level, I'm looking at a column that has 'durations'. I need to have the duration highlighted in one of three colors based on a range for each color. For example, if the value is 10 or less then it's green. If it's between 10 and 15 then it's orange and if it's greater than 15 then it's red. That part would be easy, however the challenge is that the ranges are different for the various other criteria I need to look at. I need to look at multiple columns to determine what numbers should be included in those ranges.

For example
The range will be different if the 'rev type' is REV1, the 'stage' is STAGE1 and the 'vertical' is TECHNOLOGY.

The colors to use are set up in 2 tables right now. One table for REV1 and one for REV2. Within each table there might be 4 stages listed along the left. Along the top will be verticals listed and each vertical is split into the 3 ranges.

So essentially if an item has REV1 and the stage is STAGE1 and the Vertical is TECHNOLOGY then look at the duration and if the duration is less than x then it's green if it's between x and y then it's orange and if it's greater than y then it's Red. Each stage and vertical would have a different set of metrics to determine whether the color should be green, orange or red.

I hope that makes sense and I appreciate ANY help that can be provided. Thanks in advance for your help.

This can be done through VBA. Please provide the conditions in detail.

Originally Posted by arlu1201

This can be done through VBA. Please provide the conditions in detail.

The spreadsheet that was attached to the original post essentially has the detail of how the color coding should work.

Assume my data has 5 columns.
Column A - Rev Type
Columb B - Stage
Column C - Duration (which is the column we want to color code)
Columd D - Vertical

Based on the spreadsheet in the original, I need to color code the Duration field based on the color matrix in the spreadsheet. The spreadsheet in the original only has the color code table and not actual data. I didn't think that was necessary. Let me know if you still need more.

Ok. The numbers that i see in the color code table are - for e.g 42, 40, 17, 26 (column B - Rev type 1). How do i translate these into the color codes? Shouldn't i be working with data like - from 0 to 9 = green, 10 to 15 = orange, etc?

Originally Posted by arlu1201

Ok. The numbers that i see in the color code table are - for e.g 42, 40, 17, 26 (column B - Rev type 1). How do i translate these into the color codes? Shouldn't i be working with data like - from 0 to 9 = green, 10 to 15 = orange, etc?

Imagine there is another sheet that has the actual data. The sheet that was attached to the original post has just the metrics by which the color coding is based. Each vertical will have 3 numbers for each Stage. If the value is less than the first number then the duration value in the other sheet will be green. If the value is between the first number and the 2nd number then the duration on the other sheet will be orange. If the value is greater than or equal to the 3rd number then the duration on the other sheet will be red.

Assume that the data on the other sheet looks like this.

Stage Rev Type Vertical Duration
Stage 1.0 Rev 1 Technology 45

Our color reference table from the sheet that was originally attached would tell us that the above duration should be highlighted Orange because it's Stage 1.0 for Technology and falls between 42.3 and 49.3.

Rev 1 Technology
Stage 1.0 42.3 49.3 50.3
Stage 2.0 39.6 46.6 47.6
Stage 3.0 16.7 23.7 24.7
Stage 4.0 26.3 33.3 34.3

I'm sorry...I should have been more clear in the original post. I'm hoping that this provides more information.

Thanks

Yeah its much clearer now. Will it be possible to provide a sample of the data that needs the conditional formatting? This way, it will be faster to understand how to code it.

Originally Posted by arlu1201

Yeah its much clearer now. Will it be possible to provide a sample of the data that needs the conditional formatting? This way, it will be faster to understand how to code it.

Please see the attached spreadsheet.

I was comparing your sample data sheet to the conditional formatting codes you had attached earlier. I see that in the conditional formatting sheet, there are only 4 verticals, while the sample data shows 8. Also, which vertical corresponds to which name? For e.g. which vertical stands for Technology, which stands for Telecom should be specified.

I also see 2 Rev Types - however which is the Rev type for New From Base, is it Rev Type 1 or 2?

Once you sort out the above questions, i can work out the code for you.

I think you forgot to attach the file.

Originally Posted by arlu1201

I think you forgot to attach the file.

The file should be there now. Sorry about that.

Hey ....

Would you prefer that the color formatting macro refers to the excel sheet for the values or should the numbers be keyed in directly into the code?

Are there chances of the numbers changing frequently that you would want them to be retained in the excel sheet?