Forum Discussion
Conditional Formatting
Some of my cells on my spreadsheet need to be the same color as the previous cell but the cell I need to be the same color will always be changing value but it will always be the same color as the previous cell. For instance, any cells that contain the text "Above Average" are orange and the following cell, regardless of the value needs to be the same color as the "Above Average" cell because the two will always be the same value. If it says "Average" and is yellow then the following cell needs to be yellow also. How do I form and apply that rule?
4 Replies
If your data like this
you may apply conditional formatting rule with formula
=OR(A2="Average",OFFSET(A2,-1,0)="Average")to your column starting from A2
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.
