Automatic rating based on user input?

[ntech]

Excel 2011 32 bit
	B	C	D	E	F	G	H	I	J
2	User Input								Automatic
3	Name	Month	Program	Starting Lesson	Last Lesson Completed	Number of Lessons		Section	Pacing
4	John Smith	September	RMK	21	41C	21		2	Below
5	Bob Jones	October	CRA	1	16C	16			On Target
Excel Scale

I am interested in setting up a formula that will automatically display a colored rating when the user inputs the requested information. The picture below gives the criteria guidelines for the rating.

I tried to use the HTML converter but it produces way too much text that crashes the post. If you would like the file let me know and I can send you a link to it.

 

[oldbrewer]

why is not john smith approaching rather than below

 

[ntech]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.9px Calibri; color: #000000}</style>I would like to develop a formula that would go in both C5 and C6 that would evaluate cells B5 and B6 to determine which code is entered, either CRA or RM1, then assign the correct colored conditional formatting criteria based on the chart below.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 13.9px Calibri; color: #000000}</style>
Note: The scores in C5 and C6 are automatically transferred from another spreadsheet.

Excel 2011 32 bit

	B	C

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Program[/TD]
[TD="align: center"]Number of Lessons Completed[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: center"]RM1[/TD]
[TD="bgcolor: #FFFF00, align: center"]14[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: center"]CRA[/TD]
[TD="bgcolor: #00FF00, align: center"]18[/TD]

</tbody>