Avatar of sandramac
sandramac
 asked on

Formatting Cells

Hello, I have a row of cells from b10:n10, that has data in this type of format 170 12G23MPH or 170 15MPH.  I need to evaluate the last two digits before MPH.  If between 35-50, then backfill that cell with color orange, if greater then 50 then backfill color red, else is green backfill color.
Microsoft Excel

Avatar of undefined
Last Comment
redmondb

8/22/2022 - Mon
beersince1978

Sandramac if you have Excel 2007 or later:

Go to Ribbon then "Conditional Formatting" then "Highlight cell values" and use appropriate equations.
sandramac

ASKER
What would be the appropriate formulas?
beersince1978

Does your cell look like this literally ? "170 12G23MPH"
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
beersince1978

There is "Between" formula and ">"  than (50) formula that you can use. If your data look is popluated in one cell like I described above you may consider splitting it into columns first using text to colums option in Excel.
ASKER CERTIFIED SOLUTION
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
redmondb

Hi, sandramac.

The attached needs Excel 2007+ - please let me know if you're on an earlier version.

The conditions are...
No Format --> =IFERROR(MID(A1,LEN(A1)-2,3)<>"MPH",TRUE)
Red            --> =MID(A1,LEN(A1)-4,5)>"50MPH"
Orange       -->=MID(A1,LEN(A1)-4,5)<"35MPH"
Green         -->=TRUE
(First three conditions are "Stop if True".)

Regards,
Brian.MPH.xlsx