SOLVED

Contributor

# 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?

10 Replies

Best,

# Re: Conditional Formatting based on comparing cell references

@ricardojose  here you go. Thanks

# Re: Conditional Formatting based on comparing cell references

@alex_n , here you go!

Best

# Re: Conditional Formatting based on comparing cell references

Ricardo, thanks for trying. But your solution is based on the value and not the actual cell reference. The reason behind me wanting it based on the cell reference is because sometimes the rates can be identical (ex. Current rate can be identical to the Historical rate) and it may cause bug in the result. Thanks.

# Re: Conditional Formatting based on comparing cell references

The pseudo example would be as follows:
if (the content of D2 is "=E2", then format D2 cell fill to light green color)
D2: Rate Factor
E2: Current Rate (light green color column)

I tried to use Indirect() function with combination of Address() function but could not get it to the level it would work. I may be doing something wrong here.

# Re: Conditional Formatting based on comparing cell references

Maybe you will need to reconceptualize the solution to work as needed. The only thing I can think of to achieve that and I know is possible is through macros. Are you allowed to convert that file into a macro-enabled workbook?

# Re: Conditional Formatting based on comparing cell references

See the attached version.

# Re: Conditional Formatting based on comparing cell references

Hans, thank you. Your solution is value-based as well. The way I see, a cell reference-based solution would help here.
best response confirmed by alex_n (Contributor)
Solution

OK, try this.

# Re: Conditional Formatting based on comparing cell references

Hans, this one works as expected. Thank you very much!