+ Reply to Thread
Results 1 to 3 of 3

Highlight consecutive cells with same data based on criteria

  1. #1
    Registered User
    Join Date
    03-26-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    2

    Highlight consecutive cells with same data based on criteria

    Hi,
    I've tried searching the forums but I cant quite find what I'm after.
    Is there a way to highlight cells that consecutively contain the same data, but only if the data is entered across 20 columns consecutively?
    We have to track driver hours to ensure they are taking a break after 5 hours of driving. We have a spreadsheet that our admins use to assist with monitoring but it requires them to enter an 'x' in each column for hours worked, breaks and non-drive time. I want the 'Work time' rows to automatically highlight if more than 5hrs (20 columns) have an 'x' inputted so they don't have to go back and manually count each entry.

    I've attached an example spreadsheet. You can see in row 5 that the driver worked 5.5hrs before taking a break, in this instance we would want columns AF-BA to be highlighted, or if its easier, columns AZ-BA as it still shows that the driver exceeded the allowable work hours. Is this possible?
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,477

    Re: Highlight consecutive cells with same data based on criteria

    I have re-created all conditioning formating formulas
    For work time:
    =AND(D5="x",$B5="Work time")
    Non work time
    =AND(D5="x",$B5="Non-Work time")
    Break
    =AND(D5="x",$B5="Breaks")
    And, Worktime>20 hrs
    =AND(D5="x",$B5="Work time",COLUMNS($A:D)-IFERROR(AGGREGATE(14,6,COLUMN($D5:D5)/($D5:D5=""),1),3)>20)
    All apply to same range =$D$5:$CU$62
    Attached Files Attached Files
    Quang PT

  3. #3
    Registered User
    Join Date
    03-26-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    2

    Re: Highlight consecutive cells with same data based on criteria

    You are amazing! thank you!!

+ 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: 1
    Last Post: 09-29-2022, 02:00 AM
  2. [SOLVED] Highlight Cells based on Criteria
    By scrabtree23 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2020, 12:42 PM
  3. [SOLVED] Highlight Specific Cells based on multiple criteria
    By rslush91 in forum Excel General
    Replies: 1
    Last Post: 11-15-2018, 12:13 PM
  4. Highlight consecutive rows based on value
    By olgratefuldead in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-13-2017, 05:54 AM
  5. Formula to highlight cells based on multiple criteria and sheets.
    By cascencio83 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-16-2015, 10:18 PM
  6. [SOLVED] How to highlight consecutive dates meeting multiple criteria
    By divi123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-17-2014, 08:04 AM
  7. Replies: 1
    Last Post: 11-26-2009, 12:02 PM

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