+ Reply to Thread
Results 1 to 11 of 11

Color code groups of 4 rows if data in a certain column is in a specific order

  1. #1
    Registered User
    Join Date
    03-16-2021
    Location
    Minnesota
    MS-Off Ver
    365
    Posts
    5

    Color code groups of 4 rows if data in a certain column is in a specific order

    I have a spreadsheet usually running several thousand rows that needs to color coded based on whether or not data in a column is in a particular order. If they are, that grouping of 4 rows is a "match" and needs to be colored.

    In the attachment, the "Trans Type" column contains the data that needs to be in a specific order. If a group of 4 rows has the following text in order then it needs to be color coded (alternate colors if possible like in the example)

    Salary Advance
    1608
    Salary Advance
    Payroll Deduction Taken

    If the rows in the spreadsheet do not contain those 4 phrases in that specific order in the "Trans Type" column, it doesn't need to be color coded.

    I've a hit a wall and can't figure this out. Any help is appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,454

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Hi and welcome

    I'm pretty sure this could be done with some VBA. Are you open to that idea or are you looking for a formula / conditional formatting approach only?

    BSB

  3. #3
    Registered User
    Join Date
    03-16-2021
    Location
    Minnesota
    MS-Off Ver
    365
    Posts
    5

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Quote Originally Posted by BadlySpelledBuoy View Post
    Hi and welcome

    I'm pretty sure this could be done with some VBA. Are you open to that idea or are you looking for a formula / conditional formatting approach only?

    BSB
    Hi BSB,

    Unfortunately I have no experience with VBA, it's on my list of training so for the time being, a formula/conditional formatting approach would be better. Although I wouldn't turn down VBA if that's what required and apply it down the road once I have some experience with it.

    Thanks

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    If it helps this will highlight the first row of the "block"

    Select column A

    CF

    =AND($H2="Salary Advance",$H3=1608,$H4="Salary Advance",$H5="Payroll Deduction Taken"

    Fill of your choice
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-16-2021
    Location
    Minnesota
    MS-Off Ver
    365
    Posts
    5

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Quote Originally Posted by JohnTopley View Post
    If it helps this will highlight the first row of the "block"

    Select column A

    CF

    =AND($H2="Salary Advance",$H3=1608,$H4="Salary Advance",$H5="Payroll Deduction Taken"

    Fill of your choice
    This is great! It's so close, there has to be a way to also color the other 3 rows in the block so it's all highlighted. Single rows just leaves too much empty space to identify the outliers, I really need the whole block colored in as well.

    Thank you though!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    I could not find a way of filling in the block of 4 using CF: I accept just having the first line id not really an adequate solution. I'll try again!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
    [B][I]

    Clear colours and click on "Color Code" button.

    This code could be invoked by any changes made to Sheet1 if required.
    Attached Files Attached Files
    Last edited by JohnTopley; 03-19-2021 at 04:29 AM.

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    6,454

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Paste the below code into a standard module, change the sheet name in red to the actual sheet name of your file and run it.
    Please Login or Register  to view this content.
    In the attached version I've added a button to sheet1 that when clicked will run the macro for you.

    I've also been trying to accomplish this with conditional formatting only but could only get it to work by using helper columns.

    BSB
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-16-2021
    Location
    Minnesota
    MS-Off Ver
    365
    Posts
    5

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    You both are f*n amazing! They work. Thank you both sooo much! You've literally saved me an entire day's worth of menial work. Thank you!!!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Good news!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Registered User
    Join Date
    03-16-2021
    Location
    Minnesota
    MS-Off Ver
    365
    Posts
    5

    Re: Color code groups of 4 rows if data in a certain column is in a specific order

    Done and done. Thank you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-13-2020, 02:14 AM
  2. Take out the gray color in cell tab order code
    By RJ1969 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2019, 10:44 PM
  3. [SOLVED] Modify code to duplicate only rows with specific data/value in Column A
    By jealoui in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2015, 10:07 AM
  4. Replies: 9
    Last Post: 07-31-2014, 09:56 PM
  5. Code to insert rows in next sheet in order of Column B
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2014, 10:24 AM
  6. [SOLVED] Enter text in column a based on data in several other columns for groups of rows
    By mrswain in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-27-2012, 03:29 PM
  7. [SOLVED] Macro to color groups of cells based on column values
    By juskojj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2012, 10:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1