Excel Formula help

[Adam804t]

I am new to excel and have some issues, i have made a tracking sheet to track student exams and assessments. When a student completes an exam i input there percentage mark into a cell next to their name[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]60%[/TD]
[TD]40%[/TD]
[TD]70%[/TD]
[TD]100%[/TD]
[TD]80%[/TD]
[TD]Pass[/TD]
[TD]RPL[/TD]
[TD]Pass[/TD]
[/TR]
</tbody>[/TABLE]
the pass criteria is 60% or greater, the word "Pass" or "RPL"
What i would like to do is when the cells relating to the topics meet any of that pass criteria i would like the cell with their name in to turn yellow. Obviously the example above would not change as one of the cells does not have pass criteria.
Can anyone help with this?

Adam

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this Conditional Formatting (Use a formula ..) does what you want. If you need detailed steps, post back.
You would select A2:A?? and apply the CF shown.

If this is not highlighting the correct cells, explain which ones are wrong and why.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	Name 1	60%	40%	70%	100%	80%	Pass	RPL	Pass
3	Name 2	60%	80%	70%	100%	80%	Pass	RPL	Pass
4	Name 3	61%	61%	61%	61%	61%	61%	61%	61%
5	Name 4	61%	61%	61%	61%	61%	59%	61%	61%
6	Name 5	Pass	RPL	Pass	Pass	Pass	Pass	Pass	Pass
7	Name 6	100%	100%	100%		100%	100%	100%	100%
8	Name 7	Pass	PRL	Pass	Pass	Pass	Pass	Pass	Pass
CF Pass

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =COUNTIF($B2:$I2,">=0.6")+COUNTIF($B2:$I2,"Pass")+COUNTIF($B2:$I2,"RPL")=COLUMNS($B2:$I2)	Abc

 

[Adam804t]

Hi that looks like is will do what i want it to however i have just realised (and in hindsight should have explained earlier on) that the arrangements of cells i have made is more complicated is there a way i could send you a copy of my document for you to look at?

 

[Peter_SSs]

Exchanging files privately is not permitted by the Forum Rules - see #4
Is there any reason you couldn't post a small screen shot (with dummy data) like I have above & explain in relation to that? My signature block below has a link with more details.

Failing that you could upload your file to a file-share site and provide a link. However, many of the experienced helpers here choose not to download files from other sites or due to security restrictions at workplaces, are unable to download such files so you will get less potential helpers that way

 

[Adam804t]

[/URL][/IMG]


Hi this is a screen shot of the excel i made as you can see it is a little more complex as at the start the columns that have the pass criteria do not follow on directly as i have boxes in between to record attempts.

 

[Peter_SSs]

Hi this is a screen shot ..

Unfortunately, with that sort of screen shot I cannot copy/paste the data to test with, hence my earlier suggestion of other methods.

In any case it "looks" like not only do you have cells in between (we could probably cope with that) but in row 3 in particular you appear to have a mixture of merged and unmerged cells. If that row can have such a mixture and if such cells can be anywhere in the row, that would makes things very difficult I expect.