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
jfdinneenAsked:
Who is Participating?
 
Patrick MatthewsCommented:
I was able to get this working using a combination of a UDF and Conditional Formatting.

1) Add this function to a regular VBA module:

Function RetrieveSplitItem(Index As Long, Expression As Variant, Optional Delimiter As String = " ", _
    Optional Limit As Long = -1, Optional Compare As VbCompareMethod = vbTextCompare, _
    Optional FailIfNoDelimiter As Boolean = False) As Variant
    
    ' Function by Patrick G. Matthews
    
    ' This function is a wrapper for the VB6/VBA Split function, thus allowing it to be used in
    ' the user interface for Excel and/or Access.  Depending on the Index value, the function
    ' returns a single delimited value, or an array of the various delimited values
    
    ' Arguments:
    
    ' Index indicates which element to return.  If zero, function returns the full array.  If >0,
    ' function returns the Nth delimited item, starting with 1 (even though the lower bound of
    ' the array is zero).  If <0, function returns the Nth to last delimited item.  If the
    ' absolute value of Index is greater than the number of elements in the array, function
    ' returns a subscript out of range error
    
    ' Expression is the delimited string expression to be split
    
    ' Delimiter is the character/sequence of characters used to delimit the string expression
    
    ' Limit places a cap on the number of elements returned by the function.  Default -1
    ' indicates that there is no limit
    
    ' Compare indicates whether to match the Delimiter as non-case-sensitive (default, or 1),
    ' or case-sensitive (vbBinaryCompare from VBA, or 0)
    
    ' FailIfNoDelimiter controls how function behaves if the Delimiter is not found.  Default
    ' (false) is to use the regular Split functionality, and still return an array with a
    ' single element so long as the string expression is not a zero length string.  If True,
    ' if the Delimiter is not found then the function returns a subscript out of range error
    
    Dim arr As Variant
    
    ' Set default return value of the function.  9 corresponds to 'subscript out of range'
    
    RetrieveSplitItem = CVErr(9)
    
    ' Check for presence of Delimiter
    
    If Not (InStr(1, Expression, Delimiter, Compare) = 0 And FailIfNoDelimiter) Then
        
        ' Create initial array using Split
        
        arr = Split(Expression, Delimiter, Limit, Compare)
        
        ' Depending on Index, choose the appropriate return value
        
        Select Case Index
            
            ' For Index = 0, just return the whole array
            
            Case 0
                RetrieveSplitItem = arr
            
            ' If positive, return the Nth element of the array.  If Index is too large,
            ' return an error value
            
            Case Is > 0
                If (Index - 1) <= UBound(arr) Then
                    RetrieveSplitItem = arr(Index - 1)
                End If
            
            ' If negative, return the Nth to last element of the array.  If absolute value
            ' of Index is too large, return an error value
            
            Case Else
                If (UBound(arr) + Index + 1) >= 0 Then
                    RetrieveSplitItem = arr(UBound(arr) + Index + 1)
                End If
        End Select
    End If
    
End Function

Open in new window



2) Select B26:N45, and create a formula-based CF rule using the formula

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

Set the Font formatting to Italic, strikethrough, and red color
0
 
Patrick MatthewsCommented:
John,

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

Patrick
0
 
jfdinneenAuthor Commented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jfdinneenAuthor Commented:
Patrick,

That is outstanding! Much appreciated.

Best wishes,

John
0
 
Patrick MatthewsCommented:
Glad to help, John.

Methinks it's time to write an article about that function :)
0
 
jfdinneenAuthor Commented:
Patrick,

Definitely. It is a thing of beauty.

Best wishes,

John
0
 
jfdinneenAuthor Commented:
Patrick,

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

Best wishes,

John
 Q-27421907-X.xlsm
0
 
Patrick MatthewsCommented:
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
0
 
jfdinneenAuthor Commented:
Patrick,

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

Best wishes,

John
0
 
jfdinneenAuthor Commented:
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
0
 
jfdinneenAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.