Excel 2010 Conditional Formatting individual cells based on a different column in same row
Posted on 2014-02-06
Hi, I have a spreadsheet (15 columns displayed) that has a series of columns and then various actual figures that I want to compare to a budgeted figure that will be in an earlier column but on the same row.
Let's assume figures are in the following:
M8 = Source Qtrly Budget = 200
Y8 = Actual Qtr 1 = 150
AI8 = Actual Qtr 2 = 200
AT8 = Actual Qtr 3 = 225
BE8 = Actual Qtr 4 = 200
In Y8, AI8, AT8, and BE8 I want the highlighting to be as follows:
Green background = Actual for the quarter is exactly as figure in M8
Yellow background = Actual for the quarter is MORE than figure in M8
Red background = Actual for the quarter is LESS than figure in M8
So in the example, I would want Y8 to be Red, AT8 to be yellow, and AI8 & BE8 to be green.
The issue I have is that I need to have a lot of comparisons so the solution needs to be workable for having nearly 400 rows of data, and 9 "source" comparisons for each row (that are then compared quarterly). I'm hoping that someone can help with Visual Basic code or something that is workable that Excel won't want to take 5 minutes to crunch through each time something is updated.
Thank you so much!