Change Color of cell based off value in separate column?

[Excel_Noob_2019]

Hello!

I have 2 columns of data, one with a Value and the other with a date. The Value is computed based off this formula: "=IF(L16>J16,"1","")&IF(ISBLANK(L16),"0",""&IF(L16=J16,"ON TIME","")&IF(L16<J16,"EARLY","")). Now I am trying to change the color of L16 to red if the first is true.

Essentially, if = 1, change other cell color to red.

I have not been able to figure out how to do this.

 

[Joe4]

Welcome to the Board!

You didn't say which cell this formula is in. Let's say it is in cell K16.
If you wanted L16 to be red in the formula in K16 returns the text value of "1", then you can use this Conditional Formatting formula in celll L16:
=K16="1"
and then choose the red formatting color.

 

[Excel_Noob_2019]

The formula is in cell N4, but it is copied throughout an entire spreadsheet. Is it possible to use that logic and have it still work or will I have to conditionally format each individual line that has this formula in it?

 

[Joe4]

You can apply conditional formatting to a whole range of cells at once.
The question is this: are all the cells you want formatted looking at the SAME cell, or are they looking at the a cell the will be the same relative distance away (i.e. always looking 12 rows up and 2 columns to the right)?

If you are not sure, please list out the first few, what cells the formulas are in, and what cells their coloring should be dependent on.

 

[Excel_Noob_2019]

Thank you for the welcome!

No, the cells I want formatted are looking at different cells in the same Column but different rows. They are looking at the cells that have the same Row number under Column L. So the formula is in Column R, the cells it is looking at are in Column L, with different Row Numbers.

Example,

R21 has a value of LATE, and I need L21 to turn Red.
R29 has a value of ON TIME, and I need L29 to turn Green.
R50 has a value of EARLY, and I need L50 to turn Pink.

I got it to do it with just one value, but trying to copy the conditional formatting got weird and would not work.