Gas mileage chart

New Contributor

I have a column range for varying gas prices in  column G  Is there a way to search the column to find lowest pump price and mark in green font  to find the highest pump price and mark in red font in the same column simultaneously 


4 Replies
best response confirmed by Hans Vogelaar (MVP)



Yes. Conditional formatting would do that. As shown here. You'll need to modify the formulas to fit your specifics (I did the reverse of your color scheme, for one), but this illustrates the "how".




Let's say the gas prices are in G2:G40.

Select this range. G2 should be the active cell in the selection.


On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula


Click Format...
Activate the Font tab.
Select green as highlight color.
Click OK, then click OK again.


Repeat these steps, but with the formula


and red as color.

@mathetes  Thank you for your help.  It worked, I've done it and it worked   Last time I had contact with learning Excel was about 40 years ago when I think it was then called (Lotus1-2-3) and I knew with progress I was very sure that advancements in the program have expanded  Thank you again, I'll have to find a "Excel for Dummies" book (any suggestions) to reeducate myself from the basics on up.  It's such a fantastic program on its own and with the Microsoft family


Jack (CDMKR)



You and I must be same generation. I started with Lotus as well (and, by the way, I suspect Microsoft would not look kindly on your saying that Excel "was then called Lotus 1-2-3"). It was Lotus, certainly, that took the fledgling PC industry and made it corporate-cool by giving us all the ability to do our own financial analyses. Over the years I worked at home with Quattro, another spreadsheet contender, put out by the Borland company; Lotus at work, but ultimately Excel in both places. I retired twenty years ago next month....and continue to have fun with Excel.


As to your question, there is a volume entitled "Excel for Dummies" (there's a "____________ for Dummies" on just about every software package, I think, and they're actually pretty good. I've never used the Excel version, but I have used them for other packages). On Excel, I generally now just go to various websites (this one included). Exceljet is one that I find particularly clear and useful. Since you did use it a lot back in the early days, you might find some of the YouTube videos--on just about every feature under the sun--to be to your liking. Personally, I miss the software manuals! Used to read and re-read them, always discovering something new--"Oh, that's a way to do X more directly!"--I'd realize. There are new functions coming out regularly......and it's fun learning them. One of the reasons I like to answer questions here is that it is a way for me to learn.