Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel: Strikethrough Range When Column Cells Contain Certain Values

Posted on 2011-10-29
11
Medium Priority
?
606 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:jfdinneen
  • 7
  • 4
11 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37050768
John,

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

Patrick
0
 

Author Comment

by:jfdinneen
ID: 37050864
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 37050957
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:jfdinneen
ID: 37051037
Patrick,

That is outstanding! Much appreciated.

Best wishes,

John
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37051059
Glad to help, John.

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

Author Comment

by:jfdinneen
ID: 37051196
Patrick,

Definitely. It is a thing of beauty.

Best wishes,

John
0
 

Author Comment

by:jfdinneen
ID: 37052969
Patrick,

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

Best wishes,

John
 Q-27421907-X.xlsm
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37053241
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
 

Author Comment

by:jfdinneen
ID: 37053396
Patrick,

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

Best wishes,

John
0
 

Author Comment

by:jfdinneen
ID: 37053889
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
 

Author Comment

by:jfdinneen
ID: 37055639
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question