asked on

Excel: Strikethrough Range When Column Cells Contain Certain Values

In range $B$26:$N$45, column E contains strings of the following format: 7,12,7,5,7,8. In range $J$13:$L$18 each row contains single non-repeating integers (3, 11, 7). I need a VBA routine to check if column E of the first range contains integers from each row of the second range according to the following rules:
* First value of first range must match one of cells in first row of second range;
* Second value of first range must match one of cells in second row of second range;
* ...
* Sixth value of first range must match one of cells in sixth row of second range;
All rules are ANDed and if a row in the first range does not meet the strictures of the above rules then both italics and strikethrough are applied to that row!

Please advise?

Best wishes,

John

Patrick Matthews

John,

A sample file showing sample input/output would be helpful.

Patrick

jfdinneen

ASKER

Patrick,

Thanks for the prompt follow-up.
As requested, I attach a sample file of exam answers and student results.

Best wishes,

John Q-27421907.xlsm

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfdinneen

ASKER

Patrick,

That is outstanding! Much appreciated.

Best wishes,

John

Patrick Matthews

Glad to help, John.

Methinks it's time to write an article about that function :)

jfdinneen

ASKER

Patrick,

Definitely. It is a thing of beauty.

Best wishes,

John

jfdinneen

ASKER

Patrick,

Slight problem. Partial answers are not generating the right formatting (see attached).

Best wishes,

John
Q-27421907-X.xlsm

Patrick Matthews

I see that I misread the question slightly; the CF formula should have been:

=(COUNTIF(INDEX($J$13:$L$18,1,),RetrieveSplitItem(1,$E26,","))*COUNTIF(INDEX($J$13:$L$18,2,),RetrieveSplitItem(2,$E26,","))*COUNTIF(INDEX($J$13:$L$18,3,),RetrieveSplitItem(3,$E26,","))*COUNTIF(INDEX($J$13:$L$18,4,),RetrieveSplitItem(4,$E26,","))*COUNTIF(INDEX($J$13:$L$18,5,),RetrieveSplitItem(5,$E26,","))*COUNTIF(INDEX($J$13:$L$18,6,),RetrieveSplitItem(6,$E26,",")))=0

jfdinneen

ASKER

Patrick,

That formula did not work - it strkesthrough all rows without exception. Can you upload the corrected file?

Best wishes,

John

jfdinneen

ASKER

Patrick,

The following slight alteration appears to work correctly:

=IF(PRODUCT(COUNTIF(INDEX($J$13:$L$18,1,),RetrieveSplitItem(1,$E26,",")),COUNTIF(INDEX($J$13:$L$18,2,),RetrieveSplitItem(2,$E26,",")),COUNTIF(INDEX($J$13:$L$18,3,),RetrieveSplitItem(3,$E26,",")),COUNTIF(INDEX($J$13:$L$18,4,),RetrieveSplitItem(4,$E26,",")),COUNTIF(INDEX($J$13:$L$18,5,),RetrieveSplitItem(5,$E26,",")),COUNTIF(INDEX($J$13:$L$18,6,),RetrieveSplitItem(6,$E26,",")))=0,TRUE,FALSE)

Best wishes,

John

jfdinneen

ASKER

Patrick,

To allow for possible blanks, the following additional correction was required:
=IF(PRODUCT(IF($J$13<>"",COUNTIF(INDEX($J$13:$L$18,1,),RetrieveSplitItem(1,$E26,",")),1),IF($J$14<>"",COUNTIF(INDEX($J$13:$L$18,2,),RetrieveSplitItem(2,$E26,",")),1),IF($J$15<>"",COUNTIF(INDEX($J$13:$L$18,3,),RetrieveSplitItem(3,$E26,",")),1),IF($J$16<>"",COUNTIF(INDEX($J$13:$L$18,4,),RetrieveSplitItem(4,$E26,",")),1),IF($J$17<>"",COUNTIF(INDEX($J$13:$L$18,5,),RetrieveSplitItem(5,$E26,",")),1),IF($J$18<>"",COUNTIF(INDEX($J$13:$L$18,6,),RetrieveSplitItem(6,$E26,",")),1))=0,TRUE,FALSE)

Thanks again.

John