Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to highlight Data

    I have Debit Values in Col F and Credit values in Col G and a stock # in Col H. I would like a macro to color fill in yellow Col F:H where the Value in Col F are not the same as in Col G, but where the stock #'s are the same (The Values are not adjacent)

    Attached please find sampler data. I have manmualy color filled these to show you what the desired result should like like

    I would like a macro to do this for me

    Your assistance in this regard will be most appreciated
    Attached Files Attached Files

  2. The Following User Says Thank You to HowardC For This Useful Post:

    kaybee (2014-03-06)

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What are the rules with more than one in the credit and more than one in the debit (like stock #KAZ6212)...

    Steve

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    ..assuming that there should only be one pair of Debit/Credit values per Stock No. (and not more than one as in your sample file, and as pointed out by Steve), then you could achieve what you want with conditional formatting, as per the attached file.

    If you really, really want a vba solution, please post back.

    zeddy
    Attached Files Attached Files

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not in your sample set, but would you want it colored if the credit and/or debit did not have a matching stock No?

    Steve

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Zeddy,
    Your Cond Formatting rules seems a little too complicated to me and not exactly straightforward. Perhaps I am missing something, but something like:
    =SUMIF($H:$H,$H2,$F:$F)<>SUMIF($H:$H,$H2,$G:$G)

    seems more straightforward and is simpler...

    Steve

  7. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve & Zeddy

    Thanks for the help. Where there is more than one credit and debit linked to the same stock number and all the debits and credits = 0, then the data is not to be color-filled, only if all the debuits and credits pertaining to the stock number do not bal to zero

    Howard

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Steve & Howard

    Steve:
    Yes, your simplified conditional formula is better - I posted mine just to show it can be done without vba.
    This conditional formatting would also colour the cells "if the credit and/or debit did not have a matching stock No"

    Howard:
    The conditional formatting method would highlight ALL the pairs of debit/credits for multiple (same) Stock numbers if the TOTAL of all debits/credits didn't balance to zero.
    So if a single pair of debit/credits actually balanced to zero for a particular stock number, they will still be highlighted if another pair of debit/credits for the same stock number did not balance.

    In the case of multiple (same) stock numbers, if you wanted to NOT highlight any pair of debit/credits that were the same value, then this could be done using vba.

    zeddy

  9. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    In the case of multiple (same) stock numbers, it would be appreciated if you could write code NOT to highlight any pair of debit/credits that were the same value

    I have attached some sample data using CF (color2.xlsx) have shown what the final result should look like (color.final.xlsx)

    However I only want to highlight the last remaining items that does not match off (sorry not clearer in my earlier posts)

    Howard
    Attached Files Attached Files

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Thanks for the sample files and the additional info.
    However, for a proper solution, we still need to confirm a couple of issues:
    1. What if you have just one debit, say, 1000.00, for a particular stock number, but have say, 4 credits which amount to1000.00 total. In this case, the total credits and debits balance, but the individual amounts may be different.

    2. Or are you wanting to only match exact pairs of debits/credits for each stock number, and to only highlight those that don't match?

    I'm thinking that, in the general case, you might want to have both i.e.
    a) if the total debit/credits for any stock number are in balance, then don't highlight,
    and where the total debit/credits for any stock number are not in balance, then
    b) don't highlight any pair of debit/credit values that are the same, just those that are different

    zeddy

  11. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Your thinking is 100% correct and I quote

    a) if the total debit/credits for any stock number are in balance, then don't highlight,
    and where the total debit/credits for any stock number are not in balance, then
    b) don't highlight any pair of debit/credit values that are the same, just those that are different


    Howard

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    I will send a file with the vba to do that.
    But it is time for my tea now.

    zeddy

  13. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Take your time-your help is always appreciated. Enjoy your tea

    Look forward to receiving your code later

    Howard

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Howard

    Please check the attached file.
    NOTES:
    1. The vba code is based on the Debit, Credit, Stock No being specifically in columns [F], [G] and [H] respectively.
    2. It doesn't matter what order the records are in.
    3. Conditional formatting is NOT used, it's all done via the vba.

    I haven't done many tests, but I believe this will apply the rules, i.e.:
    a) if the total debit/credits for any stock number are in balance, then don't highlight,
    and where the total debit/credits for any stock number are not in balance, then
    b) don't highlight any pair of debit/credit values that are the same, just those that are different

    Please let me know how you get on with this.
    The code is complex! But then, so is implementing the requirement.
    (I can explain why it does what it does if you want!)

    Paste your values in columns [F:H], then click the [highlight] button..

    And my tea was lovely.
    Attached Files Attached Files

  15. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-08-28)

  16. #14
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks very much for all the effort. I have tested the code and it does exactly what I require. Glad you enjoyed your tea. I also love my tea, although I enjoy a cup of decaf coffee or Cappucino two to three times a week. Not a big coffeee drinker. Are you still based in Newcastle in the UK?

    Howard

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Howard,

    Here is another way of doing the same thing with a whole lot less lines of code (82==>29)

    Code:
    Public Sub FindUnbalanced()
    'DECLARE AND SET VARIABLES
    Dim StockGroup() As Integer
    Index = 1
    FirstRow = 2
    Lastrow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row
    '-----------------------------------------
    'FIND LAST LINE OF EACH SET OF STOCK#
    For I = FirstRow To Lastrow
        ReDim Preserve StockGroup(Index)
        If Cells(I + 1, 8).Value <> Cells(FirstRow, 8).Value Then
            StockGroup(Index) = I
            Index = Index + 1
            FirstRow = I + 1
        End If
    Next I
    '-----------------------------------------
    'COMPARE SUM OF DEBITS AND CREDITS
    Index = 1
    FirstRow = 2
    For I = FirstRow To StockGroup(Index)
        SumDebit = WorksheetFunction.Sum(Range(Cells(FirstRow, 6), Cells(StockGroup(Index), 6)))
        SumCredit = WorksheetFunction.Sum(Range(Cells(FirstRow, 7), Cells(StockGroup(Index), 7)))
        'HIGHLIGHT PAIR IF NOT EQUAL
        If SumDebit <> SumCredit Then
            For J = FirstRow To StockGroup(Index)
                If Cells(J, 7) <> Cells(J + 1, 6) Then
                    Cells(J, 7).Interior.ColorIndex = 6
                    Cells(J + 1, 6).Interior.ColorIndex = 6
                End If
            Next J
        End If
        Index = Index + 1
        FirstRow = FirstRow + 1
        If Index > UBound(StockGroup) Then Exit For
    Next I
    End Sub
    Attached Files Attached Files

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2013-08-28)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •