Applying conditional formatting on range of criteria dependent cells

[branston]

Hi

I am trying to apply some conditional formatting (to cells B3:B22 on 'main' sheet) by checking whether candidates have left the topic question "blank" ie. have NOT attempted the question in which case the 'score' cell on 'scores sheet' would be recorded as a blank (as opposed to a 0). If they attempt the question but get no marks then a '0' is recorded.

I have attached my sheet and the 'yellow' coloured cells on the 'main' sheet are what I would like the finised product to look like. At the moment, for demo purposes, I have manually highlighted them yellow.

Is this possible on a range of cells? Or is there another way of doing it with/without a VBA macro? Any help would be greatly appreciated.

Many thanks

[Fluff]

Select B3:B22 & use this formula
=VLOOKUP(B3,scores!$B$4:$D$24,3,0)=""

[branston]

Thanks Fluff - that's great.

Is it possible to do this when you have more than one candidate (as I actually have loads)?

I haven't included all the vba code on the attached .... but on my master sheet the topic list + scores for that candidate would auto-change when the candidate number is entered.

File attached again so you know what I mean.

Many thanks once again

[Fluff]

What version of Xl are you using?

[Fluff]

This should work in all versions
=INDEX(scores!$D$4:$M$24,MATCH(B3,scores!$B$4:$B$24,0),MATCH(F$1,scores!$D$ 1:$M$1,0))=""

[branston]

Thanks Fluff

[Fluff]

You're welcome