+ Reply to Thread
Results 1 to 24 of 24

Conditionally highlight cells in one column based on text values in another column

  1. #1
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Conditionally highlight cells in one column based on text values in another column

    Hello all,

    I have this macro created below by jbeaucaire (thanks again JB!) and it works awesome, but now I need one modification to it.


    Here is what the sub below CURRENTLY does
    • Find the column headers named “Customer Number” and “Paper” (always in row 1)
    • If any cell in the “Paper” column DOES HAVE the text value “letter” or “legal” equal to the last data row in the “Customer Number” column, highlight the cell GREY.
    • If any cell in the “Paper” column DOES NOT HAVE the text value “letter” or “legal” equal to the last data row in the “Customer Number” column, highlight the cell RED.
    How can the sub below be modified for this one exception…
    • Find a NEW column header named “Writing Tool”
    • If any cell in the “Paper” column DOES NOT HAVE the text value “letter” or “legal” equal to the last data row in the “Customer Number” column, highlight the cell RED BUT NOT IF, in that same row in the “Writing Tool” column the text value is “pencil” or “pen”.
    • If the cell in the "Writing Tool" column does say “pencil” or “pen” then COMPLETELY IGNORE #3 above and highlight the cell in the “Paper” column GREY no matter what.
    Thanks much!



    Please Login or Register  to view this content.
    Last edited by duugg; 09-16-2009 at 03:42 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    Where's the sample sheet to work this magic on? Make sure the sample sheet completely demonstrates appropriate before/after examples.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    I'm on it now. Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    JB,

    The workbook has been attached

    Thanks
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    Maybe this additional information might help JB or someone else to tweak the code for me?

    Basically, it boils down to this...(from the attached workbook in the previous post)


    Criteria 1 - If the text value in the "Writing Tool" column is "Pencil" or "Pen", then the cell in the same row in the "Paper" column should be turned grey no matter what text is in that cell.


    Criteria 2 - If the text value in the "Writing tool" column is blank (nothing in the cell)

    The cell color in the "Paper" column should be turned...

    a) grey - If that cell value is "Legal" or "Letter"
    b) red - If that cell value is anything BUT "Legal" or "Letter"


    Criteria 3 - If the text value in the "Paper" column is "Legal" or "Letter", then that cell should be turned grey no matter what is in the "Writing Tool" column.

    Criteria 4 - If the text value in the "Paper" column is anything BUT "Legal" or "Letter" then...

    The cell color in the "Paper" column should be turned...

    a) grey - If the text value in the "Writing Tool" column says
    "Legal" or "Letter"

    b) red - If the text value in the "Writing Tool" column says
    anything BUT "Legal" or "Letter"

    This whole sub should be case INsensitive

    Thanks!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    You don't have 4 criteria, you have two. As such, you could do this with no programming with simple conditional formatting.

    1) GREY: if writing tool = pen or pencil OR paper = legal or letter
    2) RED: everything else

    Come to think of it, that's really only ONE criteria. Just color the entire column red, then apply this CF formula to change the appropriate cells back to grey:

    Condition1: Formula Is: =OR($D2="Pen",$D2="Pencil",$G2="Legal",$G2="Letter")
    Format... Patterns: Grey color


    This isn't even worth the time to write a macro, it's so easy to just do with CF.

  7. #7
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    Hello,

    The formula route sounds good because it will be faster, but I still think I have 2 issues...

    Issue 1 - These 3 columns are never in a fixed position so the formula would need to be in the proper column..so while the formula in column "G" might work in this example, it might not work the next time I need to run this because any of these columns can be in a different position.

    Issue 2 - The formula doesn't appear to know that it should only highlight the "paper" column red (and later change to grey depending on the conditions set forth in the formula) only down to, but not sooner than the last data cell in the "Customer Number" column.

    I think those 2 things are the reasons why I needed a sub rather than a conditional formatting formula.

    Any thoughts?

    Thanks

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    Formulas are applied directly to the needed cells, so they don't "know" where to stop, you do that part. You would highlight the column range only as far as needed before inserting your red color and CF formula. It's still only 30 seconds work.

  9. #9
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    JB,

    Okay, my next mission is to find out how to...

    How do I insert a formula into a column whose highlighted range is set by the last cell with data in another column.

    I will do a search and see what I can find.

    Thanks for putting me in the right direction JB

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    With your sheet open, looking to show you one way to spot the LR (last row) of a particular column, I found it was just as easy to go ahead and show you how to insert that conditional formatting into the right column, too.

    Please Login or Register  to view this content.
    =========
    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
    Last edited by JBeaucaire; 09-19-2009 at 09:39 PM.

  11. #11
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    JB,

    Sorry for the delay. I was very excited to see this sub work in action.

    As soon as I had a chance, I tried to test this sub out and I discovered is that it's not "playing nice" with this sub here....

    Please Login or Register  to view this content.
    Which finds the column named "red" and puts a "yes" in the "red" column if any cell in that row is red.

    Your original sub (from my very first post in this thread) DOES work with the sub above.

    I tried to copy/paste special/values only the "Paper" column which didn't work and if I delete the conditional formatting from the Format/conditional formatting menu, it automatically toggles the color of that cell which renders the "red is yes" sub useless.

    I don't know what the issue is here, maybe you have some insight on this?

    Is there a way to just keep the color state of all the cells AFTER your latest sub from your last post and then remove all references to conditional formatting? Maybe that will do the trick.

    Thanks,

    duugg
    Last edited by duugg; 09-21-2009 at 12:39 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    BUMP NO RESPONSE

    Can anyone help me with this? JB must be (understandably of course) busy.

    Thanks

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    If you want macros to play nice, you have to design them that way. I don't understand what you're doing. Post one workbook demonstrating before/after with all your criteria.

  14. #14
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    Hi JB, thanks for responding!

    I AM working on a workbook example for you now, but in the meantime, essentially what I want is the "Highlight_noncompliant_cells_red" sub to still work exactly as intended....(exception below)...


    Your macro below currently does this for me

    1. Find the column "Customer Number"
    2. Find the column "Paper"
    3. Search the "Paper" column down equal to the last data cell in the "Customer Number" column.
    4. Using the search range as defined in "3" above...

    a) Highlight in RED, any cell in "Paper" column whose text value is NOT "legal" or "letter"
    or

    b) Highlight in GREY, any cell in "Paper" column, whose text value IS "legal" or "letter"


    And now I would like this ONE exception...

    c) Highlight in GREY, any cell in "Paper" column, if the text value in the writing tool column says "pencil" or "pen", no matter what the text value is in the "Paper" column.


    Then, the "RED" sub below which...
    Finds the column named "red" and puts a "yes" in the "red" column for any row that has a red cell in it. (Search is also down to the last data cell in the "Customer Number" column).

    I'll post a workbook to visualize the before and after as soon as I can.


    Thanks JB

  15. #15
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    JB,

    Must faster than I thought, Paper Macro v2 has been attached.

    Thanks
    Attached Files Attached Files

  16. #16
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    I have to ask..."why"? If all you're doing is putting the word "yes" next to the red cells in the paper column, why don't you do THAT instead of turning the cells red? Why are you doing the same thing in two different places?

  17. #17
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    Sure,

    Okay, here goes the explanation. I use your original macro from the first post several times, each in it's own module. You may ask "why not put them in the same module? Well, in the beginning I tried that but I got a "duplicate scope" error, so I created another "combine sub" module that calls each module and eliminated the error.


    At the time, I didn't know that if you put these lines of code...
    Please Login or Register  to view this content.
    above the sub, you can have as many subs in the same module as you want to. Having said that, all future subs that have the same "Dims" will all be in the same module.


    Anyway, I use your sub multiple times throughout my routine. Each time though, I change this line of code here...
    Please Login or Register  to view this content.
    to look for a different column


    and change this line of code here...
    Please Login or Register  to view this content.
    to change the criteria to search for.


    After all variations of the "paper" sub are run, I get a nice list that allows me to make a quick scan of compliant cells versus non-compliant cells. I know that if cells are grey, not only are they compliant, BUT...they have also been checked for compliance, they have been "looked over".

    I use this sub for data integrity. If cells have no data or incorrect data, it highlights ONLY the non-compliant (errored) cells red.

    So, post macro, my result is many many columns that have been turned from white to grey, with a few "red" cells along the way.

    After all the variations of the subs have been run, I then have a 90%+ worksheet of grey cells.

    I then said to myself, how can I delete any rows that don't have even one RED cell in them? So I came up with the idea to create another column named "RED". The "RED" macro puts a "YES" in the "RED" column anytime a cell that's highlighted RED is found in that row and then delete any rows that don't have "Yes" in the "RED" column.



    And that is what this sub here does... although, I'm still not 100% confident that the portion of code (done by the macro recorder) highlighted in blue is "bug proof" for 2 reasons

    1. I'm not sure of the relevance of the "X5" reference
    2. Not sure if the search STOPS when it's equal to the last data row in the "Customer Number" column because it seems to take a long time to run even if I have, let's say just 5 errors.



    Please Login or Register  to view this content.


    I hope that explanation was clear, anymore questions, please ask.

    Thanks!

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    It's taking a long time because you are evaluating the entire column one row at a time. You don't need to loop through all the entire range of the RED values and delete rows one at a time. You only need to highlight the entire range of values, find cells with a constant in them (the word "red" is a constant) and delete the rows, all at once.

    Are you simply wanting to put borders around the remaining cells? Perhaps X5 showed up when you recorded because at the moment you recorded, that was your "Red" column. You have to keep context in mind when reviewing recorded code. It always need editing to make it less "specific".

    This code does the deletion of all "red" entries in the RED column all at once:
    Please Login or Register  to view this content.
    Tell me what is supposed to happen on the "Errors Only" sheet and I can help there, too.

  19. #19
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    Ahh, sorry if I wasn't more clear when I said this below...

    I then said to myself, how can I delete any rows that don't have even one RED cell in them? So I came up with the idea to create another column named "RED". The "RED" macro puts a "YES" in the "RED" column anytime a cell that's highlighted RED is found in that row and then delete any rows that don't have "Yes" in the "RED" column.


    In other words...
    The "RED" macro I currently have puts a "YES" in the red column if any cell in that row is COLORED red. After the macro has done it's search equal to the last cell with data in the "Customer Number" column, the active worksheet will have like 90% grey colored cells with a few scattered RED colored (colored red, NOT the text value "RED") cells throughout the worksheet.

    I then make a copy of that worksheet named "Errors only" and it is at THAT point that I want to delete any rows THAT DON'T HAVE ANY RED COLORED CELLS IN THEM. This is currently done by deleting any row that has a "yes" in the "red" column. In case you're wondering, yes, I do mean the entire row.

    The end result is that the "Errors only" worksheet now presents me with a list of customers that have a red colored cell somewhere in that row (because all rows that don't have any RED colored cells in them have been deleted).

    Keep in mind that your original macro (from my first post in this thread) works perfectly with all that I have said above.

    All I need at this point is to somehow implement the "Unless" condition that the new "Writing Tool" column has presented, which colors any cell in the "Paper" column grey no matter what, providing that the text value in the "Writing Tool" column is "Pencil" or "Pen". Of course, the macro should stop when it is equal to the last data cell in the "Customer Number" column, no sooner, no later.

    Case INsensitive.

    Whew and Thanks JB

  20. #20
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    AHH, This line here..

    This is currently done by deleting any row that has a "yes" in the "red" column
    Should say

    This is currently done by deleting any row that DOES NOT HAVE a "yes" in the "red" column

    Sorry about that

  21. #21
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    Maybe this:
    Please Login or Register  to view this content.

  22. #22
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    JB,

    Thanks but I still need the "Writing Tool" condition (turning some cells red and some grey) to coincide with this macro. Can you combine them into one macro?

    Thanks

  23. #23
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Conditionally highlight cells in one column based on text values in another colum

    Just add the line:
    Please Login or Register  to view this content.
    ...before the end of the other macro so it runs this macro for you.

  24. #24
    Valued Forum Contributor
    Join Date
    04-11-2006
    MS-Off Ver
    2007
    Posts
    438

    Re: Conditionally highlight cells in one column based on text values in another colum

    lol,

    No, I mean the whole sub below that includes the "writing tool" column modification where if the data values in the writing tool column are "pen" or "pencil" then turn the cell in the same row in the "Paper" column Grey, no matter what.

    The macro below works perfectly, but, as explained in my previous posts in this thread, I need to modify it to include the "writing tool" column.

    Thanks

    Please Login or Register  to view this content.

+ 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