Conditional Formatting for multiple columns/rows that are not sequential

jsgarner19

New Member
Joined
Apr 19, 2012
Messages
6
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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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:
Upvote 0
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)

excelpic_zpseff868c9.jpg


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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :)


 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top