Change Color of cell based off value in separate column?

Excel_Noob_2019

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
2016
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,502
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
2016
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,502
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 6, 2019
Messages
3
Office Version
2016
Platform
Windows
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.
 

Forum statistics

Threads
1,082,510
Messages
5,365,993
Members
400,865
Latest member
LuciaRomo

Some videos you may like

This Week's Hot Topics

Top