Conditional formatting based on numbered text

[Guest]

What is the best way to apply conditional formatting in the following case?
I have a column with validated text fields (users can select one of seventeen
choices). I have them numbered and would like to format the cell red if the
text begins with the numbers 1 through 5, yellow for text starting with 6
through 11, and green for anything higher.

Thanks in advance for your help!

 

[Biff]

Hi Anne!

Can you post an example of what the text string actually
looks like for each of the conditions? We'll need to know
that in order to provide a solution.

Biff

 

[David McRitchie]

Hi Anne,
You will not be able to use Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
because you have more than 3 colors.

Suggest your next best alternative if a case statement
in a change event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#case

I have included the 56 default colorindex colors alongside
the example, but that information was extracted from my
color page. http://www.mvps.org/dmcritchie/excel/colors.htm

 

[Biff]

you have more than 3 colors.

1-5 = red
6-11 = yellow

12 = green

Or am I not reading the post correctly?

Biff

 

[David McRitchie]

Hi Biff (and Anne),
Sorry I saw the number seventeen, you're right 3 colors
would have been easier to read if it had been posted like you rewrote it.

Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Conditional formatting is by far the best choice.
Now it back to that part about if the cell begins with.

you have more than 3 colors.

1-5 = red
6-11 = yellow

12 = green [actually over 11]

Or am I not reading the post correctly?

Biff

-----Original Message-----
Hi Anne,
You will not be able to use Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
because you have more than 3 colors.

Suggest your next best alternative if a case statement
in a change event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#case

I have included the 56 default colorindex colors alongside
the example, but that information was extracted from my
color page. http://www.mvps.org/dmcritchie/excel/colors.htm





.

 

[David McRitchie]

1-5 = red

6-11 = yellow
12 = green [actually over 11]

If A2 is the activecell when entering Conditional Formatting

Condition 1: =VALUE(LEFT(A2,2))>11 [Green]
Condition 2: =VALUE(SUBSTITUTE(LEFT(A2,2),".",""))>5 [yellow]
Condition 3: =VALUE(SUBSTITUTE(LEFT(A2,2),".",""))>0 [Red]

The cell options are:
1. Obtain manager's approval
2. Completion of either TEI Technology Overview OR TEI CRM Foundations
3. Completion of the TEI Field Orientation Seminar
4. Completion of Bloodborne Pathogens for CRM Clinical/Research Personnel
5. Documentation of a current negative TB test
6. Documentation of Hepatitis B vaccination
7. Communicate with FCE host and set a date for visit
8. Complete the TEI Field Hosting Program Application
8a. Rank learning objectives
8b. Sign application
8c. Obtain manager's signature
8d. Give copy to Anne Barrett (retain original)
9. Implementation
9a. Travel
9b. Complete TEI Field Hosting Program Evaluation
9c. Communicate Evaluation with manager
9d. Give to Anne Barrett

after a very recent change. Is this still doable?

Thank you all for your help!

Hi Biff (and Anne),
Sorry I saw the number seventeen, you're right 3 colors
would have been easier to read if it had been posted like you rewrote it.

Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Conditional formatting is by far the best choice.
Now it back to that part about if the cell begins with.

you have more than 3 colors.

1-5 = red
6-11 = yellow
12 = green [actually over 11]

Or am I not reading the post correctly?

Biff

-----Original Message-----
Hi Anne,
You will not be able to use Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
because you have more than 3 colors.

Suggest your next best alternative if a case statement
in a change event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#case

I have included the 56 default colorindex colors alongside
the example, but that information was extracted from my
color page.
http://www.mvps.org/dmcritchie/excel/colors.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

What is the best way to apply conditional formatting in
the following case?
I have a column with validated text fields (users can
select one of seventeen
choices). I have them numbered and would like to
format the cell red if the
text begins with the numbers 1 through 5, yellow for
text starting with 6
through 11, and green for anything higher.

Thanks in advance for your help!


.

 

[David McRitchie]

correction: groups are >11, 6-11, 1-5

If A2 is the activecell when entering Conditional Formatting
Condition 1: =VALUE(LEFT(A2,2))>11 [Green]
Condition 2: =OR(LEFT(A2,2)="10",LEFT(A2,2)="11",VALUE(LEFT(A2,1))>5) [yellow]
Condition 3: =VALUE(LEFT(A2))>0 [Red]

1-5 = red
6-11 = yellow
12 = green [actually over 11]

If A2 is the activecell when entering Conditional Formatting

Condition 1: =VALUE(LEFT(A2,2))>11 [Green]
Condition 2: =VALUE(SUBSTITUTE(LEFT(A2,2),".",""))>5 [yellow]
Condition 3: =VALUE(SUBSTITUTE(LEFT(A2,2),".",""))>0 [Red]
--- -

The cell options are:
1. Obtain manager's approval
2. Completion of either TEI Technology Overview OR TEI CRM Foundations
3. Completion of the TEI Field Orientation Seminar
4. Completion of Bloodborne Pathogens for CRM Clinical/Research Personnel
5. Documentation of a current negative TB test
6. Documentation of Hepatitis B vaccination
7. Communicate with FCE host and set a date for visit
8. Complete the TEI Field Hosting Program Application
8a. Rank learning objectives
8b. Sign application
8c. Obtain manager's signature
8d. Give copy to Anne Barrett (retain original)
9. Implementation
9a. Travel
9b. Complete TEI Field Hosting Program Evaluation
9c. Communicate Evaluation with manager
9d. Give to Anne Barrett

after a very recent change. Is this still doable?