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 content is only available to members.

To access this content, 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)>"50MPH"

Orange -->=MID(A1,LEN(A1)-4,5)<"35MPH"

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.