Forum Discussion

alex_n's avatar
alex_n
Brass Contributor
Mar 09, 2023

Conditional Formatting based on comparing cell references

Hi All,

 

I have a rate spread sheet (below) that has 7 columns:

 

 ABCDEFG
1Cost DescrCost AmountRate AppliedRate FactorCurrent Rate
(light green)
Live Rate
(light golden)
Hist. Rate
(light blue)
2Material79,13226,01333%33%30%32%
3Labor20,3065,78428%28%29%29%
4Equipment78,16415,01619%19%21%27%
5Subcontract9,7832,94430%30%22%22%
6Miscellaneous61,16716,92128%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.

Resources