Forum Discussion
Conditional Formatting based on comparing cell references
Hi All,
I have a rate spread sheet (below) that has 7 columns:
A | B | C | D | E | F | G | |
1 | Cost Descr | Cost Amount | Rate Applied | Rate Factor | Current Rate (light green) | Live Rate (light golden) | Hist. Rate (light blue) |
2 | Material | 79,132 | 26,013 | 33% | 33% | 30% | 32% |
3 | Labor | 20,306 | 5,784 | 28% | 28% | 29% | 29% |
4 | Equipment | 78,164 | 15,016 | 19% | 19% | 21% | 27% |
5 | Subcontract | 9,783 | 2,944 | 30% | 30% | 22% | 22% |
6 | Miscellaneous | 61,167 | 16,921 | 28% | 28% | 25% | 28% |
I want to be able to set conditional formatting on column D where it changes the fill color based on its reference: if I reference D2 to the live rate (F2), its fill color will change to light golden, the rest goes respectively or reference D3 to the current rate (E3), the fill color of cell D3 should become light green. I use different rates for different costs. I want the user to visually see what rate is applied to certain cost.
Please note that the condition must be based on the actual reference and not the values because sometimes the values of multiple rates may be identical.
I tried to come up with a formula that compares the cell contents in cell D2 to the address of cell F2 but couldn't get it to work.
Expected result - column D
Any idea?
Thanks in advance.
- ricardojoseBrass ContributorHello, would you mind uploading your file so I can use it to add the things you need there and then I can upload the solution?
Best,- alex_nBrass Contributor
- ricardojoseBrass Contributor