+ Reply to Thread
Results 1 to 4 of 4

Highlighting data that is less than a specific cell by rows

  1. #1
    Registered User
    Join Date
    09-16-2016
    Location
    cape town
    MS-Off Ver
    2013
    Posts
    2

    Highlighting data that is less than a specific cell by rows

    Hi there

    i have a long list of data, basically, from b3 to e3 there are 4 catagories: a date, total number, relevant number, percentage of relative number. then these 4 catagories repeat for 15 times (for each different month). after this i have calculated the total average percent, and then halved it. i want to select and highlight all the ones in each row that are below the halved average.

    i have been doing this by selecting each row, conditional formatting, cells that are below then selecting the cell with the halved average (for egaxple, bq3). however, i have to do this for each row separately. i was wondering if there is a way to select the whole table (from b3: bq46) and have each row highlighted if it is below the corresponing value in BV (so b3:bp3 will highlight all below bq3, b4:bp4 will highlight all below bq4...)?

    any help would be appreciated!




    M SU DONORS JUN % C IM M SU DONORS JUL % C IM M SU DONORS AUG % C IM M SU DONORS SEP % C IM TOTAL AVE TOTAL AVE AVE% C IM
    Jun-16 513 2 0.39% Jul-16 778 4 0.51% Aug-16 498 1 0.20% Sep-16 430 6 1.40% 30 4.29 113 8.07 1.46% 0.73%
    May-16 937 2 0.21% Jun-16 513 2 0.39% Jul-16 778 1 0.13% Aug-16 498 8 1.61% 27 3.86 87 6.21 1.15% 0.58%
    Apr-16 569 4 0.70% May-16 937 16 1.71% Jun-16 513 9 1.75% Jul-16 778 16 2.06% 70 10.00 134 9.57 1.76% 0.88%
    Mar-16 603 5 0.83% Apr-16 569 7 1.23% May-16 937 12 1.28% Jun-16 513 21 4.09% 63 9.00 132 9.43 1.66% 0.83%
    Feb-16 561 3 0.53% Mar-16 603 5 0.83% Apr-16 569 43 7.56% May-16 937 41 4.38% 84 12.00 179 12.79 2.22% 1.11%
    Jan-16 307 9 2.93% Feb-16 561 7 1.25% Mar-16 603 12 1.99% Apr-16 569 7 1.23% 77 11.00 138 9.86 1.89% 0.94%
    Dec-15 207 8 3.86% Jan-16 307 5 1.63% Feb-16 561 17 3.03% Mar-16 603 11 1.82% 69 9.86 144 10.29 1.98% 0.99%
    Nov-15 797 4 0.50% Dec-15 207 1 0.48% Jan-16 307 16 5.21% Feb-16 561 16 2.85% 59 8.43 122 8.71 1.55% 0.77%
    Oct-15 381 9 2.36% Nov-15 797 4 0.50% Dec-15 207 7 3.38% Jan-16 307 6 1.95% 66 9.43 146 10.43 1.84% 0.92%
    Sep-15 287 3 1.05% Oct-15 381 5 1.31% Nov-15 797 6 0.75% Dec-15 207 2 0.97% 47 6.71 131 9.36 1.60% 0.80%
    Aug-15 430 2 0.47% Sep-15 287 3 1.05% Oct-15 381 17 4.46% Nov-15 797 9 1.13% 56 8.00 119 8.50 1.34% 0.67%
    Jul-15 767 5 0.65% Aug-15 430 1 0.23% Sep-15 287 8 2.79% Oct-15 381 5 1.31% 63 9.00 125 8.93 1.45% 0.72%
    Jun-15 544 6 1.10% Jul-15 767 2 0.26% Aug-15 430 8 1.86% Sep-15 287 1 0.35% 39 5.57 109 7.79 1.18% 0.59%
    May-15 528 6 1.14% Jun-15 544 1 0.18% Jul-15 767 6 0.78% Aug-15 430 4 0.93% 53 7.57 130 9.29 1.47% 0.73%
    Apr-15 401 18 4.49% May-15 528 6 1.14% Jun-15 544 9 1.65% Jul-15 767 9 1.17% 56 8.00 112 8.00 1.20% 0.60%

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Highlighting data that is less than a specific cell by rows

    Similar requirement: http://www.excelforum.com/excel-gene...ll-values.html

    Select whole table from row 3 down and use formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Note that the only absolute part is $BQ
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-16-2016
    Location
    cape town
    MS-Off Ver
    2013
    Posts
    2

    Re: Highlighting data that is less than a specific cell by rows

    thank you so much- youre fantastic!!!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Highlighting data that is less than a specific cell by rows

    You're welcome.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. [SOLVED] Highlighting multiple rows through a worksheet depending a cell value in the rows
    By loked in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2014, 01:31 AM
  2. Replies: 2
    Last Post: 06-13-2013, 12:16 PM
  3. [SOLVED] Highlighting a cell when specific conditions met
    By jpsprack in forum Excel General
    Replies: 5
    Last Post: 07-05-2012, 06:22 PM
  4. [SOLVED] Excel 2012, Help With Highlighting Specific Rows
    By DrNerj in forum Excel General
    Replies: 5
    Last Post: 06-01-2012, 09:52 AM
  5. Highlighting specific decreasing trend data
    By -aki- in forum Excel General
    Replies: 0
    Last Post: 05-30-2011, 02:37 AM
  6. Highlighting cell when DOB is after specific date
    By usmccstimp in forum Excel General
    Replies: 1
    Last Post: 01-29-2011, 11:38 PM
  7. Highlighting specific data in a graph
    By grey_fox33 in forum Excel General
    Replies: 2
    Last Post: 10-27-2010, 04:26 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