Conditional Formatting Separate Columns

I am attempting to apply conditional formatting for two separate columns but with the same conditions.

When I apply the following formula:

Please Login or Register  to view this content.

Capture.JPG

Everything works as expected for column U, but not for column D.

In column D some cells get the formatting, but others do not.



Anybody have suggestions?

Thanks for taking a look!.

Here is what i see:
Missing Formatting.JPG

Some of the 18131 have the formatting, some don't.

11/26/16 was a typo, should have been 11/26/13

Yes, I understand the correlation of AE and Item # in the same row.

Ultimately i don't understand why it seems to be working just fine for Column U, but when i apply the same CF to Column D, it does not appear to work.

Originally Posted by Tsjallie

I think this part is the culprit.
Technically it's OK, but apparently functionally it's not because the results are not what you expect.
Also you must understand that the condition does not look at AE1, but at the date in the cell in column AE which is in the same row as the item#.
So if the item is in cell D6 it looks at the date in cell AE6. In that case the condition is true. But e.g. for the item in cell D8 the date in cell AE8 = 8/12/14.
BTW, datevalue 41604 is 11/26/13.

So I think with AE1 = 11/26/16 you actually mean to establish something else.

Ok, maybe i can take a step back.

Goal: If AE5 = 11/26/13 AND TS_List! A:A contains the same data as U5: Format U5 and any cell in D that matches U5. The Cell in D may NOT be in the same row.

Reason for the fixed date- it signifies whether or not an action needs to occur. Any date (or blank) that is not 11/26/13, no action needs to be taken, and thus does not need to be Formatted.

Perhaps VBA would be a better avenue.

No, I just have to change my focus to a higher priority project. Back burner for a while...

Ok, after some coffee, a nights sleep and re-reading this thread- I get what you are saying about the relationship of IE and Item in the same row. And, you are right it IS working correctly, I just wasn't getting the result i was expecting.

Back to the beginning-

Columns R thru AE are a list of work orders i need to schedule
R is a work center
AE is an indicator of the last time a time study was conducted.

The Conditional Format will identify which Item Numbers need a new time study: It compares sheet TS_List Column A item numbers to U AND has a TS date of 11/26/13.
CF Formula for Column U: =AND($AE1=41604,(CountIf(TS_List!$A:$A,$U1))) - This works

The Copy button on each row- This matches the Work Center in R to a Work Center in B, then finds the next open row(i believe this is what causes the CF to not work as expected) and pastes the data S thru AC.

All i want- If the CF formatted a Cell in U, when it is copied to new row in B, keep the formatting.

CF Formats the cell, not the data in the cell so when copied, it does not move with the data.

In the example, S6:AC6 where "18131" is formatted, it could land in B(any row):L(any row) when the copy button is clicked. So, its relation to the criteria breaks down.

Since I'm using a macro to Copy to its new location anyway... perhaps VBA is a better alternative?

Interesting solution. Excel amazes me just about every day with what can be done...

Thanks again!