Change row color without conditional formatting

  • hey, busy day, just checked the file.....it work perfectly but...

    ok let me try and explain

    so the macro i had someone help me with, its function is to copy and auto populate the next available row in outstanding balances sheet only if date delivered and date collected is filled but not date paid, and for the overweight fee, if filled, it will auto fill the next available row in overweight sheet. So when i click on the outstanding balance sheet, the row will also be copied yellow from the main.


    the file you sent me does exactly that except it copies the first color(red) used in the row from the main sheet, not the last color(yellow) if that makes sense. for example main sheet row will be yellow, but when auto copied, the row in overweight sheet will stay red not yellow......


    hey i just want to say i appreciate all this, its been on my mind for 2 month!!

  • Thanks for your feedback.:)


    If my understanding is correct ...


    You are saying that the " Row Color Change " does function as expected ... BUT your Event macro which performs copies ... now... does need to be adjusted to this new feature ...


    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hey!!!.....Yes and it does need to be adjusted to copy exactly what's is in the row...

    OK ...


    But there is a need for clarification ...for Each Color and Each Copy regarding the modifications in Main Sheet ...


    For example, a modification in the Master Sheet will change the row color ....


    1. When does it need to be replicated


    And


    2. When does it need NOT to be replicated

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • good morning, im off work today.....ok so are you ready lol


    alright here i go


    So, the only time the row is replicated to the outstanding bins sheet is when a value is inputted in date collected column and if date paid column is blank from the main sheet.


    if there is a value inputted in date paid column, then the row is not replicated when a value is inputted in date collected column


    and the only time the row is replicated to the overweight bin sheet is when a value from the main sheet is inputted in overweight fee column




    real world example


    Customer calls and I input information for the day. When delivered the row turns red, this lets me know its a bin that hasn't been picked up


    if paid the row turns green, which means the row doesn't need to be replicated. but if i dump the bin and is overweight, i input the value row changes blue and its replicated to the overweight bin sheet so i know to contact customer.


    Now if the bin is collected and not paid for, I input the value, row changes to yellow, and is replicated to the outstanding bin sheet so I know to follow up with customers. And like before if overweight, row changes blue and is replicated to overweight bin sheet.



    ok i hope that clarifies lol

  • Thanks a lot for your clarification :)


    Next step is to translate all your comments into macro instructions ...and find out if we can achieve your final objective ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • That would be soon awesome, there is one final step. I'm not sure if it's possible.


    Online iv seen people use a click function on their column headers to sort a column instead of using the sort & filter. I was wondering if this is possible for rows.


    For example let's say I want to sort date delivered bins, I can just click on the date delivered header and will sort all the rows in red to the top based on the date it was delivered, same thing goes for date paid, date Collected, and overweight Fee. And to sort everything back to it's original position I click on the "bin #" header...is that possible?

  • Yes ... it is possible ...:)


    But ... if you agree ... let's try to get the Copy Color Challenge out of the way ... before adding the tricky Sorting feature 8o

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Based on your real life example ...


    My understanding in terms of Copy is quite clear ...


    Now ... in terms of Colors ... my summary would be as follows :


    1. The Changing Colors are ONLY changing in your Master Sheet


    2. In the Outstanding sheet ... you are keeping a List of ONLY Yellow records


    3. In the Overweight sheet ... you are keeping a List of ONLY Blue records


    Am I on the right track ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Step 1 ... the Copy Color Challenge ...


    Attached is your Version 2 ...


    One issue which might cause a new headache :


    Is there something which needs to happen whenever you come back to an existing record located in the Master Sheet to update a field...

    Are you expected this update to be reflected in any other worksheet ... talking about the Data ...not about he Colors ... ;)


    see attached file ...


    Hope this will help

    :)

  • Changing colors will happen in the master sheet and the only change of color in the other sheet will happen when i input a value in date paid, they will turn green.


    I just realized something, all the row values should be replicated from the main sheet to the overweight bin sheet except date paid column So when the overweight is paid, the row will turn green from blue....

  • sorry got distracted by phone calls

    Sorry was distracted from phone calls, I decided to delete the master sheet, won't need to keep track of previous jobs if they are done


  • 1. Copy from Main Sheet to both Outstanding and Overweight do work fine ...


    2. Changing Dates in the Main Sheet ... at my end ... does change the entire Row Color ... does it function at your end ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • its not functioning on my end, the row is stuck on green....even if i input a value for date collected and overweight fee

    Can you change the Date Collected and the Overweight Fee date ... AFTER you input in the Date Paid cell ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

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