Conditional Formatting Data based on Data in Another Column

[cgfrank]

Hello.
Every day I run an automated report from one of our systems that tells me about recent field activity from our employees (that they enter themselves into another system). I would like to find a way to highlight changes. This list is hundreds of entries long and manually reviewing it is time consuming.
In the below example, some or all of Bob's rows should be highlighted, because he changed from site A to Site B. Tom should not be highlighted, because he stayed on Site C.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C
[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TaanyaP]

I am finding that it is basing the formatting for the entire column just on the results of row 2. I have 7000 rows of data.

 

[63falcondude]

Okay, I did the exact steps you said and it didn't apply any of the formatting??!

The formulas are designed to apply formatting only when the cell in column M is not blank and when the cell in column L says "completed".

Try to copy and paste my example to a new spreadsheet and see if you can get the CF working on that first.

 

[63falcondude]

I am finding that it is basing the formatting for the entire column just on the results of row 2. I have 7000 rows of data.

It starts on row 2 and then goes down the amount of rows that you highlighted in the beginning.

 

[TaanyaP]

I copied the data to a new "test" worksheet. I then highlighted N2:N7000 and applied the conditions in the order that you asked me to, but, nothing happened. I am not sure what I am doing wrong

 

[63falcondude]

I copied the data to a new "test" worksheet. I then highlighted N2:N7000 and applied the conditions in the order that you asked me to, but, nothing happened. I am not sure what I am doing wrong

You copied the data from the sample that I created in post #9, have everything in the correct columns (should match the example), and then followed the outlined steps? If so, I do not know why it is not working for you.

 

[TaanyaP]

I copied the data to the new "test" worksheet. I then highlighted N2:N5330. I copied your formula's for each of the conditions as [I capitalized the C in completed as that is how it is in the column] you had them and set it in the order you posted. Still nothing happened. Can I send you the sheet so that you can look at it? I have removed all non essential data so no confidentiality issues.

 

[RCBricker]

Status is column L. I have been just using the last column to apply the formatting to (column N), so my formatting applies to =$N:$N using the format only cells that contain option, as follows:
Condition 1
Cell Value <=TODAY() (formatted red cell/red font)
Condition 2
Cell Value between =TODAY() and +TODAY()+30 (formatted yellow cell/brown font)
Condition 3
Cell Value between =TODAY()+30 and =TODAY()+1095 (formatted green cell/black font)

I would like to use the "Use a formula to determine which cells to format" option but don't know how to correctly write the formula.

ok you didn't offer what the status options are...

So I went with "Open" and "completed.

Here are you CFs to make this work.

First CF is to be the highest priority and have the stop if true check box marked

=$L2="Completed"

the following are the remainder of the formulas

=$N2>=TODAY()+31
=AND($N2>=TODAY(),$N2<=TODAY()+30)
=AND($N2<=TODAY(),$L2="Open")

after entering in each formula set your font and fill colors

rich