Highlighting in Conditional Formatting

Ive got one I need help with.

In column B I have a piece of equipments id #. in D,F & h is where and for who. J is the date equipment was issued and L the date equipment was picked up. I have L set to highlight B to red showing the equipment has been picked up. In column T I have all the equipment listed as to be on the yard. What i'm wanting is to highlight the T column cell if the number is listed in column B and "NOT" highlighted red showing that it is not on the yard. Hope this makes sense. If anyone could help me on this I would appreciate it.

Thanks

I'm getting confused to. Let me see if can explain better.

T is a list of tanks that is always the same to show what equipment there is.

B,D,H,J AND L is a list showing where equipment is or was to be able to track. Its always changing.

So say if I enter #10 in B showing where that tank went where ever #10 in the list in T is would highlight showing that it is out. so when looking over the list of equipment you can see that it is out.

Then when the tank is picked up a date is entered in L turning B red showing that tank picked up from that location then unhighlighting the #10 tank in T showing that it is available for the next job. Most times the tanks come back to the yard.

T needs to only highlight when a tank entered in B is not highlighted. B will show a tank several times for differant jobs. I can get T highlighted if something is entered in B. Id say I need T to highlight only if B has a white background "highlighted white".

Hope this makes my question clearer.

Thanks for your help

Q=T

B is red when L has date entered

if B is red then Q white

if B is white then Q is gray

I need Q to highlight by the same tank # entered in B not by the same row. If highlighting the same row of B and Q would be differant tank #s. Say B10 is 5 and not red then Q5 would highlight gray meaning that tank is out. Say B11 is 6 and highlighted red Q6 would be white meaning tank is in. Then say B12 was 6 showing that tank went again next then Q6 would highlight gray. Column B the number are a variable and Q the numbers are a constant.

OK, I think I understand. Try attached.

Thats it! Thank you. I will apply to my tank and see how works. I knew was a little more complicated for me to figure out. Seems so simple. Again thank you. Ill get back to you if I have any problems.

netspy

It's not hard but it's not trivial. I had to do a little research to figure out how to find the last occurrence of a tank number in column B, because a tank number can appear more than once and it's the last one that matters. The SUMPRODUCT function is very powerful and comes in handy for this. Do note that I put limits on the range (7-100) instead of using the whole column, so if you need to go past row 100 you will need to update the formula in the condition. I tried using the whole column first but then I think Excel tries to run that SUMPRODUCT function on a million rows and so Excel just stays busy all the time burning 50% CPU.

Yea excel is very powerful it self. Im learning that the more I use it. I didnt realize what all could be done. Its nice that theres a place you can ask questions and get help with something your trying to do. Thanks again.