Link to home
Start Free TrialLog in
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.
Avatar of beersince1978
beersince1978

Sandramac if you have Excel 2007 or later:

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

ASKER

What would be the appropriate formulas?
Does your cell look like this literally ? "170 12G23MPH"
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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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