Highlighting rows and using formatconditions and copy paste to different sheets

  • Hello good people,




    I am working on a database where people join and have an ID card. ID cards are expired. I want to write VBA codes to highlights an entire row based on different cell values and then copy and paste those values to different sheets according to highlighted rows.




    A snap is attached herewith for your understanding. Is there any better ways to combine all the requirements? Please suggest and help.


    Thanks in advance.




    I tried these two codes:



    [/#]


    Files

    • Book1.xlsm

      (32.6 kB, downloaded 12 times, last: )
  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Why is it necessary to move data? It's a pointless exercise when you highlight the rows that need action


    Your conditional formatting should stat in Row 5 not 2.

    I want to highlight rows according to 4 conditions based on values on column J.


    a. 45 and above - no filtered color

    b. between 30 and 45 - yellow color

    c. less than or equal to 30 - Red color

    4. any blank cell - no filtered color



    Here I exclude a condition and tried to write code for b, c, and d. These highlighted rows will be copy and pasted to the next two tabs.

    yellow ones will be pasted in ''Required Action'' and red rows will be pasted in ''Fast Action'' maintaining the resizing formatting.


    The problem I faced: If I generally filled any row with red or yellow color, copy-paste function works. If I use conditional formatting from excel, rows are nicely highlighted, BUT.... copy paste is not working on the highlighted rows already by using excel conditional formatting from the HOME tab.


    So I thought to write another code for formatconditions as well. I need to merge both vba sub-functions to make it smooth.

    Can you help?

  • Have you read the Forum Rules yet?


    Again, I see no point in copying data to multiple sheets.

    I am struggling to use the code.
    I need to copy to multiple sheets to separate this ID-based information and print out.
    As you can see, all these IDs will expire soon (dates are just assumptions, not actual ones).

    Thanks.

  • I've added code to filter and print each condition apart from empty cells because I don't understand what you mean.


    The code currentl previews but you can delete that line and use the PrintOut line

  • I've added code to filter and print each condition apart from empty cells because I don't understand what you mean.


    The code currentl previews but you can delete that line and use the PrintOut line

    Thank you!
    This simple coding makes sense. But the main conditions were missing.


    I want to highlight entire rows according to 4 conditions based on the cell values on column 'G'.


    a. 45 and above - no filtered color

    b. between 30 and 45 - yellow color

    c. less than or equal to 30 - Red color

    4. any blank cell - no filtered color


    These highlighted rows will be copied and pasted to the multiple sheets for later review with a single click. If I require it, I will print it. Therefore, I can see immediately which Staff IDs are yet to renew. Yellow ones will be pasted in ''Required Action'' and Red rows will be pasted in ''Fast Action'' maintaining the resizing formatting.


    I hope, I could make the idea clear.
    Thanks again.