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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)>"50MP H"
Orange -->=MID(A1,LEN(A1)-4,5)<"3 5MPH"
Green -->=TRUE
(First three conditions are "Stop if True".)
Regards,
Brian.MPH.xlsx
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,
Red --> =MID(A1,LEN(A1)-4,5)>"50MP
Orange -->=MID(A1,LEN(A1)-4,5)<"3
Green -->=TRUE
(First three conditions are "Stop if True".)
Regards,
Brian.MPH.xlsx
Go to Ribbon then "Conditional Formatting" then "Highlight cell values" and use appropriate equations.