PDA

View Full Version : [SOLVED] Applying conditional formatting on range of criteria dependent cells



branston
03-06-2020, 04:32 AM
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
03-06-2020, 05:48 AM
Select B3:B22 & use this formula
=VLOOKUP(B3,scores!$B$4:$D$24,3,0)=""

branston
03-09-2020, 03:06 AM
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
03-09-2020, 05:44 AM
What version of Xl are you using?

Fluff
03-09-2020, 05:50 AM
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
03-27-2020, 06:45 AM
Thanks Fluff

Fluff
03-27-2020, 06:56 AM
You're welcome