Change row color without conditional formatting

  • Excellent News ...!!! :)


    Hope your supercharged workbook will assist you ... and ease your workload ...:thumbup:

    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:)

  • Excel is indeed a truly fantastic tool ... which can make Life so much easier ...:love:

    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, one last and final thing


    I Tried double clicking the header "Bin #" in Column A


    it completely unorganized the bins i had in order in that column. So i had to rewind time lol


    Is it possible to apply the same technique so the bins are in order from the top numerically?


    starting with 4, 5, 6, 10, 14, 15, 16, and 20 Yard bins.


    Last and final tweak, sending you my completed file

  • Hello,


    Just found out your Bin # ... which was supposed to be a Bin number ... is in fact labelled as Text ...


    My question would be: do you have a predetermined List of 20 Categories ( similar to your 9 Bin Types) or not ... ?


    In addition, it would seem that these cells are not manually typed in ... but copied from another source which does add invisible line break characters : char10 ... and also unwanted blanks : char32 ...

    As a consequence, Sorting this field might become a real challenge


    Obviously, I do not know at all the field in which you operate ... and all the specifics such as the Bins Standard Sizes and Features ...

    But, for consistency purposes ... and still with the Objective to make your life easier :

    On one hand, why would you have Data Validation Lists for almost all of your different input columns ...

    and, on the other hand, would you deprive yourself of this ' insurance ' for this Bins # Column A, which appears to be:

    both one of the most important field ... and probably the trickiest ...


    Will try to prepare an initial patch for you ...:)

    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 long day, so here i go

    My question would be: do you have a predetermined List of 20 Categories ( similar to your 9 Bin Types) or not ... ?Y

    im assuming you are asking if the 9 type of bins i have in stock are the only size bins i have, if so, yes!

    In addition, it would seem that these cells are not manually typed in ... but copied from another source which does add invisible line break characters : char10 ... and also unwanted blanks : char32 ...

    yes definitely copied from another source, ill send a file so you can see exactly what i was working with from the beginning

    On one hand, why would you have Data Validation Lists for almost all of your different input columns

    might be reading this wrong, but the data validation list will help me save time using the dropdown list instead of typing it in.


    on the other hand, would you deprive yourself of this ' insurance ' for this Bins # Column A, which appears to be:

    both one of the most important field ... and probably the trickiest

    For the bin # column A, that column will stay fixed


    For example


    Row 2 - 4 Yard


    lets assume the bin is delivered and paid(Green). Customer calls and tells me the bin is ready for pickup, it turns yellow. i may Leave the bin at my yard or

    another customer calls asking for the same size bin. Right away i know its available because its yellow, I fill in the row again, bin type, price, payment type, invoice!?,extra days!?, customer info, location, and date delivered. I would have to delete the date paid cell and collected cell...........


    hmmmmmmmmm

    i wonder if i select "date delivered" cell in that row, it automatically delete the values in "Date Paid" ,"Date Collected" and "Overweight Fee" in the same row, is that possible, what you think?


    Also today i noticed when looking for bins that are due on the 7th day or past 7 the day rental period, it was a bit hard to see.


    Question, can the letters its self turn "white" in the rows on the "7th day" and beyond" from the selected date for "date delivered" in "Column I" and if a "extra day" is added to "Column F", it will turn "white" on the "8th day and beyond", with 2 extra days, on the 9th day and beyond and so on?



    And to turn the row letters back to black in that row, the "Date Collected" cell in that row needs to be filled in?


    just when i thought i had everything covered, there was something else!!!!!!!


    they say a picture is worth a thousands words, you my friend you are worth way more!!!!!!!!!!!!



    ohhh here is the file that started it all lolololol

  • Thanks for your comments


    Talking about your 19 Categories ... please see Column F in sheet Settings ...


    Is this list complete or not ...?


    Question is important as it 'feeds' both the Newly created DropDown feature in Column A ...

    and it allows the Double-Click feature required to Sort ... now added to Column A ...

    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:)

  • Time for a recap ...


    Given the new features you keep on adding ... the complexity is increasing, mainly because of overlapping conditions ...


    Let's focus initially on the Main Sheet :


    A. The Double-Click in Row #2 will trigger the Sort by Color in the Last four Columns, and to give you added flexibility, in Column N, you can select either Ascending or Descending, which will allow to Sort by Values ... within Each Color

    ( as you know, the Sequence of Colors is determined by the selected Header ...the one you do double-click on)


    B. The Double-Click in Cell A2 will trigger a custom-made Sort which relies on a custom order set in Sheet Settings in Column F


    C. The Double-Click in Column ' Date Delivered ' will automatically delete data input of the last four columns (I,J,K,L) - and also remove the interior color of the Entire row


    D. The Double-Click in Row #2 in Columns B,C,D,E,F,G,H will trigger a ' standard' Sort by Values


    E. In both the Outstanding Bins and Overweight Bins worksheets, adding a Date Paid will change interior color to Green


    F. In both the Outstanding Bins and Overweight Bins worksheets, the same Sort features provided by Double-Click are available


    G. Specific to the Main Sheet, there are Five change features:

    1. Column D : for Non-Cash Payments, there is a 13% Sales Tax added automatically to the Bin Price in Column C

    2. Column F : for each Extra Day, $15 per day is added automatically to the Bin Price in Column C

    3. Column I and J : for Date Delivered and Date Paid, interior color of entire row is adjusted for any input

    4. Column K : for Date Collected, interior color of entire row is adjusted and copied to its own worksheet...

    after two conditions are fulfilled : i.e. No Date Paid and a Date Delivered

    5. Column L : for Overweight Fee, interior color of entire row is adjusted and copied to its own worksheet


    H. Your last request " 7th Day and beyond " ... a formula has been added to turn the font color to white...


    Quite obviously, all of these modifications do require to be validated ...

    so, only your thorough tests will help identify what still needs to be corrected ...


    Hope you will enjoy your last Version 6

    :)


  • works perfectly

    yep this list is completed :thumbup:



    A. The Double-Click in Row #2 will trigger the Sort by Color in the Last four Columns, and to give you added flexibility, in Column N, you can select either Ascending or Descending, which will allow to Sort by Values ... within Each Color

    ( as you know, the Sequence of Colors is determined by the selected Header ...the one you do double-click on)

    Sounds Good!!

    B. The Double-Click in Cell A2 will trigger a custom-made Sort which relies on a custom order set in Sheet Settings in Column F

    Right on

    C. The Double-Click in Column ' Date Delivered ' will automatically delete data input of the last four columns (I,J,K,L) - and also remove the interior color of the Entire row

    works perfectly, can we switch that around instead and add more cells


    for "Date Delivered" only deleting I,J,K,L can we also add columns B,C,D,E,F,G,H,I,J,K,L to be deleted


    and


    whats your opinion!?


    Im thinking about switching the double clicking feature to Column "Bin #" or "Date Collected" instead of using "Date Delivered"


    Kinda leaning more to "Bin #" cells

  • and this is all correct!!!!

  • OK ...


    Only modification included in the attached last Version 6.1 :


    Whenever any row in Column A is Double-Clicked, all contents in Columns B to L get deleted and the entire row gets no interior color


    Glad to hear you have finally reached the finish line ...:S

  • hey was juts double checking the file


    and I noticed the double clicking color sort feature for "Date Paid" Column J, sorts the rows in red for "date delivered" instead of green


    Also

    Whenever any row in Column A is Double-Clicked, all contents in Columns B to L get deleted and the entire row gets no interior color

    can we add this for


    Outstanding bins sheet, and Overweight Fee sheet?

  • Despite an earlier warning about sticking to the colors you had picked, Just found out you have selected a different color for Date Paid ... which, by the way, you have modified in the Main Sheet ... creating mismatches with the other worksheets ...

    so basically all Sort on Color macros have to be again re-adjusted ...


    Will make the ... very very ... last modifications for you ...

    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:)

  • Wait wait i think I explained it wrong, when I double clicked date paid (green), it sorted the rows in red instead of green that's what I meant


    Do no worry ... your explanation was extremely clear ...;)


    The thing I explained in my previous message is ... the reason why ...


    The Green Color is no longer working ... for a single reason : you have changed it ...!!!

    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!