Conditional Formatting with Excel 03

**sulavsingh6** · 08-27-2009, 11:23 AM

In a column in my spreadsheet i have to input a number; either: 51-61 and next to it is either m or o. so in Column A row 1 : 51m, Column A row 2 60m, etc
Example:
51m
51o
55o
60m

I want to conditionally format this such that for 51 it turns the cell background green, for 52 red, 53 orange,etc. with a number having a specific color associated with it. 03' does not have a text choice. I was wondering how to use the formula is part of conditional formatting to accomplish this.

Thanks

**NBVC** · 08-27-2009, 11:27 AM

LEFT(A1,2) extracts the number as text....

CF: =LEFT(A1,2)="51"

or LEFT(A1,2)+0 extracts the number as number

CF: =LEFT(A1,2)=51

**sulavsingh6** · 08-27-2009, 12:05 PM

i tried that and for some reason it is not working. I have attached the sheet to this. Please let me know what i am doing incorrectly

**NBVC** · 08-27-2009, 12:16 PM

Your forumula has quotes around the whole thing.. should only be around the number

e.g

=LEFT(G1,2)="51"

Note: You can't have more than 3 in 2003... not sure if using 2007 as your sample is .xls not .xlsx

**sulavsingh6** · 08-27-2009, 01:03 PM

Thanks that worked.

**NBVC** · 08-27-2009, 01:08 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save