Hide rows based on date and colour

  • Hi everyone,
    I have the table in excel with some claims (one line is one claim) where the cells in column A are filled with green colour when the claim was done if not the cell is yellow. In column C, there are the dates of receive the claim. What I need is to hide rows (claims), where in column A is green colour but I don´t wanna hide any rows in actual month. There are 2 conditions: 1) Don´t hide anything from actual month 2) Hide remaining rows where are cells in column A filled with green colour. I solved how to hide rows with green colour but I don´t know how to add to this macro the conditons that the month shouldn´t be actual. . . .


    For hiding I´m using this code:

    Thank you very much for your help. I uploaded excel file.


    forum.ozgrid.com/index.php?attachment/36925/

  • Re: Hide rows based on date and colour


    This should do what you want - hide rows where cell in column A is filled green and the date in column C is not in the current month.
    You have done most of the work so your macro only needs a little tweek.



    Hope this helps
    Anthony

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Hide rows based on date and colour


    Thank you very much for your help. . . but it doesn´t work :( I have a problem with the line "compareMonth = .........." system is writing: "Run-time error 13: Type mismatch", "Can´t execute code in break mode". Anyway thank you very much. . . The date in column C is in this format 4.1.2011 and the lines with claims are begining on the 10th line.

  • Re: Hide rows based on date and colour


    Hello polmaster,


    Welcome to Ozgrid.


    Please take some time to read the forum rules.


    All VBA code posted in the forum must be wrapped in code tags which you omitted. I've added the tags for you this time only. Be sure to use them in future posts.


    [COLOR="navy"]How to use code tags[/COLOR]


    [noparse]

    Code
    [/noparse]
    [COLOR="navy"]your code goes between these tags[/COLOR]
    [noparse]

    [/noparse]


    Thanks.

  • Re: Hide rows based on date and colour


    How is the fill color in column-A cells getting set? Are you using conditional formatting? Is this a value, such as Yes (= done), or Complete , etc?


    What you need to accomplish can, likely, be done more efficiently using autofilter, applying criteria to the date column and the "status" column.


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments

  • Re: Hide rows based on date and colour


    I´m sorry for my mistakes and thank you for your advice. . . I promise I´ll be better in my next post. . . Also I uploaded excel file where I need use this macro into my first post in this thread. . . Thanks a lot

  • Re: Hide rows based on date and colour


    What is the criteria for setting the color of the cells in column-A?
    Is this just done manually. I don't see where any conditional formatting is being used or any obvious reference to other cells to set this.
    So, how do you decide if cell A1 is to be yellow or green?

  • Re: Hide rows based on date and colour


    Which column is "concrete claim" and what values decides yellow or green?
    Please be explicit and provide details. In order to apply a filter, rather than an inefficient loop, we must establish specific criteria for the filter.

  • Re: Hide rows based on date and colour


    Ok, this table contains all claims in the year 2011 and we are completing it during the year. This table is printed to walls in our company and it must be updated very often. What I need to solve is the problem with printing. . . When I´m printing updated version there must be all claims from current month (green, yellow) and the remaining claims from months elapsed only yellow (no green). Yellow = claim wasn´t sorted out yet, Green = claim was sorted out. Up to the present day I did it manually (hide rows with green cells in column A from elapsed months). . . The concrete claim is described in column H. Yellow or green are decided by concrete employee. . .When our company has some claim, employee will write it to this file and fill in the cell with yellow coulour. If he has done it he will open this file again and manually fill in this cell green instead of yellow.

  • Re: Hide rows based on date and colour


    I have added the code to your claims spreadsheet - see if this works for you. I have tried it and it works for me.
    Let me know


    Hope this works
    Anthony

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Hide rows based on date and colour


    Glad you got a solution, however:


    Filtering would be MUCH MORE EFFICIENT, but as you did not provide enough information this approach could not be implemented.
    In place of manually coloring the cells you could Conditional Formatting to automatically do this. Then, the criteria for the conditional format could, likely, be used with the filter.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!