auto highlight intersection of select columns and rows

[palaeontology]

I have the following comparison matrix where up to 26 student submissions are to be compared against eachother, one at a time, for example, submission A was compared with B and B won, A was compared with C and C won, etc etc etc.

and I have the following dynamic list of letters that are not to be compared at all ...

What I would like is for the comparison matrix to fill (in black) any row or column associated with any letter found in that list. For example, in the list at the moment are the letters D E and H, so I would like the matrix to look like this ...

Is this possible ?

Kind regards,

Chris

 

[palaeontology]

oops, I meant the dynamic list (at the moment) should be just E and H (not D E and H)

 

[palaeontology]

i'VE CREATED A LIST OF COLUMNS AND ROWS TO BE HIGHLIGHTED IF A CERTAIN LETTER APPEARS IN THE LIST .....

so for example, as the letters E and H are currently in the list, I would expect the following ranges to be filled with black shading ..

K6:K9
L10:AF10
N6:N12
O13:AF13

Is there a way to code it so that it reads the "Applications not to be considered " list and black-highlights the corresponding 2 ranges given (in the "To be shaded" lookup list above) for each letter in the "Applications not to be considered" list ?

Kind regards,

Chris

 

[palaeontology]

ok, I've been unable to piece together a suitable vb code to do this, so have had to muscle the problem a different way.

I have now created a lookup table that lists (for each submission letter A to Z) all the cells that should be colour-filled if that letter is in the 'do not consider' table ...

currently, the program is saying submissions E and H should not be considered, so the next table shows the vlookup result of which cells should now be colour-filled ...

so, I've now tried to use that list of cells to be colour-filled (with black) in a conditional format using the following conditional formatting formula applied to the range $H$6:$AF$30...

=IF(COUNTIF($CH$6:$DF$31,ADDRESS(ROW(),COLUMN(),4),>0

but it's saying there's a problem with the formula.

Can anyone see how I should rewrite the formula ? To help you, I'll re-insert an image of what the matrix should look like once the appropriate cells have been colour-filled ....

Very kind regards,

Chris