Forum Discussion
Conditional Formatting
SergeiBaklan Thank you for your response. So my data looks like the following:
| Date | Overall Quadrant | Overall Score | Incentive Quadrant | Incentive Score |
| 9.27.2019 | Above LCL | 5.07 | Above Average | 7.25 |
So Row 2 Column 2 and Column 3 need to be the same color just as Row 2 Column 4 and Column 5 need to be the same color. The values in Column 3 and Column 5 will always be the same color as Column 2 and Column 4 regardless of the number. So Above LCL is orange and so is 5.07 and Above Average is yellow and so is 7.25. If it were to drop down to below average or any other description then the colors set for those will either be red or green and the following column's value would match the values set for below average and would also be the same color as the color for below average. I guess the simpler way to say this is the Overall Quadrant and Overall Score will always be the same color as will the Incentive Quadrant and the Incentive Score. Sorry, I didn't mean to make that confusing.
Could you please clarify the starting point - why your cell, let say Above LCL as in example, is orange or green or red? Do you apply conditional formatting to it use some formula or color manually? In first case you may expand the logic on other cells, if the latest then only with VBA programming.
SergeiBaklan So this spreadsheet is the daily scorecard for all agents at my job. We have been provided a Mac for employment purposes only and are not allowed to email anything from our work computer to our personal email or anything like that. So I have pulled my row out of the spreadsheet and am manually entering the data daily based on the scorecard and the color key is as follows:
Red is Below LCL, orange is Above LCL, yellow is Above Average, and green is Above LCL. The first four columns of colored cells are labeled as follows: Overall Quadrant and Overall Score which will always be the same color based on what the scores are. If the scores are Below LCL then both cells for that day in those two columns will be red. If the scores go up to Above LCL then those two cells for that day in those two columns will be orange. Then the next two columns of the four columns are labeled as follows: Incentive Quadrant and Incentive Score and the same rules apply for the color code depending on the score. The remaining columns I am able to apply conditional formatting based on the cut-off point for those percentages. I am just recreating an already created spreadsheet but am unable to copy and paste due to the policies in place which doesn't allow the copying or emailing of data. So I just need to extend the conditional formatting for the Overall Score to match the color of the Overall Quadrant, which is determined on a scale of 1-10 with 1 being the lowest and 10 being the highest. Anything that is 1 standard deviation below the average score is Above LCL (orange), anything that is 1 standard deviation above the average score is Above Average (yellow), Anything that is 1 standard deviation above Above Average is Above UCL (green) and anything that is 1 standard deviation below Above LCL is Below LCL (red). The same rule applies to the Incentive Quadrant and the Incentive Score. So I just need to know how to make a rule for the Overall Quadrant which will always produce a result that is text and the Overall Score which will always produce a result that is numeric to make both columns be the same color based on the scores which will show whether it's Below LCL, Above LCL, Above Average, or Above UCL. Hopefully, this answers your question. If not please let me know what other information I can provide. Thank you!