Posts by Rurkz

  • Copy a row from one spread sheet to another with conditions

    Hey so im looking for a lil bit of help here,


    Early in the year i had some help create a bin log file for me on this site, eventually i realized i was missing one more function.


    Ok so in the 1st picture i provided "Main Sheet", i had the rows from A-L copied to the next available row in Outstanding Bins only if column "I" (Date Delivered) is filled and then column "k"(Date Collected) is filled in afterwards. You can see this in the second picture i provided for "Outstanding Bins".


    What i want to accomplish also is have rows from A-L copied to the next available row in "Completed jobs" only if column "I" (Date Delivered) is filled in, column "J" (Date Paid) is filled in, and then column "k"(Date Collected) is filled in afterwards.


    I Tried brainstorming this on my own and could not figure it out since im a complete noob, here is the code provided from the Main sheet Tab



    Option Explicit


    '''''''''''''''''''''''''''''''''''

    ' Version 6.2 - dated 11 Mar 2021 '

    '''''''''''''''''''''''''''''''''''


    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' Use Mouse Left Double-Click to Sort Ascending on Any Header Title

    ' AND

    ' in Column Date Delivered ... it deletes data in 4 last Columns I to L

    If Target.Column > 12 Then Exit Sub

    If Target.Row = 2 Then

    Select Case Target.Column

    Case 1

    Application.Run ("CustomSort")

    Case 9 ' Red

    Call SortByColour1(Target)

    Case 10 ' Green

    Call SortByColour2(Target)

    Case 11 ' Yellow

    Call SortByColour3(Target)

    Case 12 ' Blue

    Call SortByColour4(Target)

    Case Else

    Call StandardSort(Target)

    End Select

    Else

    ' Whenever Column A (i.e. #1) - sheet Bins # is Double-Clicked '''''''''

    If Target.Column <> 1 Then Cancel = True: Exit Sub

    Dim x As Long: x = Target.Row

    ' Clear Contents in Columns B to L

    Range(Cells(x, 2), Cells(x, 12)).ClearContents

    ' Delete Interior Color

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = xlNone

    End If

    Cancel = True

    End Sub



    Private Sub Worksheet_Change(ByVal Target As Range)

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    ' Objective of this Event Macro :

    ' to copy and auto-populate the next available row in Outstanding Bins sheet

    ' ONLY if date delivered (Column K) AND date collected (Column I) are filled BUT NO date paid (Column J) ...

    ' For the Overweight Fee (Column L), if filled, it will auto-fill the next available row in overweight sheet.

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    If Target.CountLarge > 1 Then Exit Sub

    If Target.Column > 12 Then Exit Sub

    Dim lRow As Long, j As Long

    Dim x As Long: x = Target.Row

    Dim y As Long: y = Target.Column


    ' Deal with Exceptions : If input is deleted from any of the Last 4 Columns ...

    ' Interior Color for Entire Row MUST be re-adjusted ....

    ' To be aligned with Conditional Formatting rule ....

    ' Revert Font Color from White to Black

    If y > 8 And IsEmpty(Target) Then

    With Range(Cells(x, 1), Cells(x, 12))

    .Font.Color = vbBlack

    .Font.Bold = False

    For j = 9 To 12

    If Not IsEmpty(Cells(x, j)) Then

    .Interior.Color = Cells(2, j).Interior.Color

    Exit For

    Else

    .Interior.Color = xlNone

    End If

    Next j

    End With

    Exit Sub

    End If

    ' Standard Process

    Select Case y

    Case 4

    ' Add Sales Tax 13% for Non-Cash Payments

    If Target <> "Cash" Then Target.Offset(0, -1) = Target.Offset(0, -1) * 1.13

    Case 6

    ' Add $15 for each Extra Day

    If Target >= 1 Then Target.Offset(0, -3) = Target.Offset(0, -3) + (15 * Target.Value)

    Case 9, 10

    '

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    Case 11

    ' Outstanding Bins

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    With Sheets("Outstanding Bins")

    lRow = .Columns(1).Find("*", After:=Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row + 1

    ' No Date Paid and Yes Date Delivered

    If Target.Offset(, -1) = "" And IsDate(Target.Offset(0, -2)) Then

    Range(Cells(x, 1), Cells(x, 12)).Copy Destination:=.Range("A" & lRow)

    End If

    Target.Activate

    End With

    Case 12

    ' Overweight Bins

    Range(Cells(x, 1), Cells(x, 12)).Interior.Color = Cells(2, y).Interior.Color

    With Sheets("Overweight Bins")

    lRow = .Columns(1).Find("*", After:=Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious).Row + 1

    Range(Cells(x, 1), Cells(x, 12)).Copy Destination:=.Range("A" & lRow)

    Target.Activate

    End With

    End Select

    End Sub

  • Change row color without conditional formatting

    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

    Quote from Carim

    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?

  • Change row color without conditional formatting

    and this is all correct!!!!

  • Change row color without conditional formatting

    works perfectly

    yep this list is completed :thumbup:



    Quote from Carim

    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!!

    Quote from Carim

    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

    Quote from Carim

    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

  • Change row color without conditional formatting

    hey long day, so here i go

    Quote from Carim

    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!

    Quote from Carim

    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

    Quote from Carim

    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.


    Quote from Carim

    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