Link to home
Get AccessLog in
Avatar of 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

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


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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
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".)