Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Conditional Formatting for multiple columns/rows that are not sequential

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting for multiple columns/rows that are not sequential

    Hello all,
    I have searched hoping to not have to bother anyone with this question, but haven't found one that quite matched up with my needs.

    I am looking for a way to highlight a single cell of 3 columns (columns E, I and M), and 300 rows (starting with row 2)that is the lowest number in each row.

    For instance Conditional formatting works wonderfully when I highlight cells E2, I2, and M2 choose Conditional Formatting>top/bottom rules> bottom 10 items > change to bottom 1, then OK.

    It works perfectly, except, I don't want to have to ctl click e3,i3,m3 then follow the steps above for each and every row for 300 rows.

    So, in summary, every row needs to highlight the lowest number, down the entire sheet.

    This is for comparisons between pricing of 3 different food vendors, so that we can choose from month to month the lowest pricing. Not sure if this is going to end up being more work inputting the values each month, but if that is the route we should choose, I want to be able to, at a glance, see the cheapest vendor for a given food product.

    Any help would be appreciated.

    Thanks

    Jim
    Last edited by jsgarner19; Jan 13th, 2013 at 01:16 AM.

  2. #2
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    mmmm....ok....if I'm understanding correctly....you try selecting the columns first, M then I then E, with them selected go to conditional formatting, use a formula and enter
    Code:
    =E2=MAX($E2,$I2,$M2)
    and select the format.
    this should give you the Max for each row and highlight the cells if there are duplicates

    Sorry, a small change to the formula....

    Code:
    =IF(E2="","",E2=MAX($E2,$I2,$M2))
    which should keep any unpopulated cells clear of any formatting
    Last edited by Weazel; Jan 13th, 2013 at 01:55 AM.

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    I think that would work, but the problem is that I have numbers in almost all other columns in that row too, up to row M, and I need to only highlight the numbers in E, I and M.

    Here is a screenshot of what I am trying to do (sorry, I don't have full version of excel, so I can't DL the excel html maker as suggested in the posting guidelines)



    So, I just want the values of cost/unit to show in red, if it is the lowest cost/unit item for that item. Eggs, .24 from Sysco would highlight red for the lowest between the three vendors, Pancake mix, sysco would highlight red, etc, etc on down the page.

  4. #4
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    the trick to making it work is to ensure you have all 3 ranges selected before you add the conditional formatting.

    Also you need to ensure that you select column E last so that E2 is active when you add the formula.

    so essentially select column M, hold down the CTRL key and select I, then select column E, then add the formula to conditional formatting.

    I've tested it several times and it will highlight the highest value across each row, and will also highlight duplicates in that row.

  5. #5
    New Member
    Join Date
    Apr 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    Thanks Weazel,
    Appreciate the help on that. It worked like a charm.

    I do have one question though, and I know this is going to sound idiotic, but HOW does it work.

    If you have the time and/or inclination to educate me on how this particular formula is doing what we are asking it do, I would like to know.

    =IF(E2="","",E2=MAX($E2,$I2,$M2))

    I get =if(e2= .... what I don't get is what the "" are for, is that saying that ANYTHING can be in the cell, regardless of value, for the formula to look at it. the next part E2=MAX is fine, (I had to change it as I needed MIN not MAX), and the ($e2,$I2,$m2) is saying don't move off of column E,I, and M but do follow the rows down, correct? I'm just stumped on the "", "" i guess.

    Again, thanks for your help, it's going to make life so much easier.

    Jim

  6. #6
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    Actually I think that was a mistake on my part.

    If I remember correctly I was having a time of it stopping the blank rows in the range from taking the formatting and wasn't sure of the best way to stop it, so I figured if I gave conditional formatting the if blank, leave it blank, it would address it. Well, that was the intention anyway.

    Though now, I can't seem to duplicate the problem and the original formula
    =E2=MIN($E2,$I2,$M2) seems to be working fine.

    Something I probably overlooked the other day, I'll have to research it a bit.

    Either way, I'm glad its working for you



  7. #7
    New Member
    Join Date
    Apr 2012
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    Ok, one other question then, since I have your ear

    I don't actually want duplicates to highlight, what would I need to change to stop that from happening? It's not a huge deal, because the numbers VERY rarely will duplicate, and I've moved the decimal out to three places to account for rounding and duplicates, but I'm wondering what would stop the conditional formatting on a formula to stop on a duplicate.

    Thanks again.

    Jim

  8. #8
    Board Regular Weazel's Avatar
    Join Date
    Dec 2011
    Location
    Florida
    Posts
    3,155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    Well the biggest issue I can see is the numbers are in non adjacent columns, so the MIN will return a TRUE for duplicates in conditional formatting.

    I think the best solution is what you are doing with the extended decimal places to make them truly unique and only 1 TRUE is returned.

    I imagine there is a potential VBA solution but I only know enough VBA to get in trouble.

  9. #9
    Board Regular
    Join Date
    Feb 2012
    Location
    Calgary, Alberta
    Posts
    56
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    Quote Originally Posted by jsgarner19 View Post
    Hello all,
    I have searched hoping to not have to bother anyone with this question, but haven't found one that quite matched up with my needs.

    I am looking for a way to highlight a single cell of 3 columns (columns E, I and M), and 300 rows (starting with row 2)that is the lowest number in each row.

    For instance Conditional formatting works wonderfully when I highlight cells E2, I2, and M2 choose Conditional Formatting>top/bottom rules> bottom 10 items > change to bottom 1, then OK.

    It works perfectly, except, I don't want to have to ctl click e3,i3,m3 then follow the steps above for each and every row for 300 rows.

    So, in summary, every row needs to highlight the lowest number, down the entire sheet.

    This is for comparisons between pricing of 3 different food vendors, so that we can choose from month to month the lowest pricing. Not sure if this is going to end up being more work inputting the values each month, but if that is the route we should choose, I want to be able to, at a glance, see the cheapest vendor for a given food product.

    Any help would be appreciated.

    Thanks

    Jim
    Hi Jim, I think we had similar requirements this week. Here is what fellow member assisted me with...

    Dim rCell As Range
    ActiveSheet.Cells(2, 1).Select
    Application.ScreenUpdating = False

    For Each rCell In Range("E,I,M") 'Red Font Colours are your requirements
    With Range(rCell, Cells(Rows.Count, rCell.Column).End(xlUp))
    .FormatConditions.Delete 'This line will "Clear" the Conditional Formatting on the whole sheet
    .FormatConditions.AddTop10 'This Line Selects the Top/Bottom Conditional Formatting
    .FormatConditions(1).TopBottom = xlTop10Bottom 'This is where you select what you want to view the Top or Bottom values in a range
    .FormatConditions(1).Rank = 1 'This is where you select "How Many"
    .FormatConditions(1).Percent = False 'Just like on the CF Home Tab you need to indicate you do not want to see %'s
    .FormatConditions(1).StopIfTrue = False 'Just like on the CF Home Tab you need to indicate you do not want to "Stop" the search
    .FormatConditions(1).Interior.Color = RGB(15, 150, 200) 'Here you input the Cell Highlighting
    End With
    Next

    ActiveSheet.Cells(2, 1).Select
    Application.ScreenUpdating = True

    End Sub
    Phil

  10. #10
    New Member
    Join Date
    Jul 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting for multiple columns/rows that are not sequential

    This worked perfectly except for one thing...how do you make the formula ignore 0 values? I have (and need) the zero values, but don't want them to be picked up my the min function.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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