+ Reply to Thread
Results 1 to 11 of 11

Highlighting in Conditional Formatting

  1. #1
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,135

    Re: Highlighting in Conditional Formatting

    I think you want to specify "use a formula" and use this:

    =AND(B1<>"",L1="")

    I'm a little confused though. If T has the equipment in the yard, wouldn't any cell in T that is nonblank indicate that the number is listed in B and not highlighted in red (meaning that L is blank)? So every nonblank cell in T is highlighted?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Highlighting in Conditional Formatting

    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

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Highlighting in Conditional Formatting

    Hi NETSPY

    Perhaps upload a workbook, with dummy data and your expected result.
    Regards Kevin


    Merged Cells (They are the work of the devil!!!)

  5. #5
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Highlighting in Conditional Formatting

    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
    Attached Files Attached Files

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,135

    Re: Highlighting in Conditional Formatting

    See attached. The rule to turn cells in column Q gray is not based on the color of B but rather on the opposite of the condition used to turn B red.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Highlighting in Conditional Formatting

    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.
    Attached Files Attached Files

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,135

    Re: Highlighting in Conditional Formatting

    OK, I think I understand. Try attached.

  9. #9
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Highlighting in Conditional Formatting

    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

  10. #10
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,135

    Re: Highlighting in Conditional Formatting

    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.

  11. #11
    Registered User
    Join Date
    07-07-2012
    Location
    OKLAHOMA
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Highlighting in Conditional Formatting

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1