Exceeding 3 Criteria Limit In Conditional Formatting

RNitschke · Jun 5th 2008

The code segment from "Get Around Excels 3 Criteria Limit in Conditional Formatting" works perfectly if I type the variable directly into the cell, but not in a range of cells.

That is to say, if I copy the source cell to a range of cells I receive a "Run-time error 13: Type mismatch" error".

Is there a way to get around this?

This is the code segment I've tried in the worksheet.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer


    If Not Intersect(Target, Range("b2:q53")) Is Nothing Then
        Select Case Target
            Case "IBBCH", "ibbch"
                icolor = 36 'light yellow
            Case "OBBCH", "obbch"
                icolor = 34 'light turqoise
            Case "OBBRDG", "obbrdg"
                icolor = 35 'light green
            Case "LNCH", "lnch"
                icolor = 53 'brown
            Case "BRK", "brk"
                icolor = 15 'gray-25%
            Case "AV", "av"
                icolor = 10 'green
            Case "AS", "as", "med", "MED"
                icolor = 3 'red
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If


End Sub

Display More

Bob Phillips · Jun 5th 2008

Re: Exceeding Excel's 3 Criteria Limit In Conditional Formatting

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Long
    Dim cell As Range
     
    If Not Intersect(Target, Range("b2:q53")) Is Nothing Then
        For Each cell In Range
            Select Case cell.Value
            Case "IBBCH", "ibbch"
                icolor = 36 'light yellow
            Case "OBBCH", "obbch"
                icolor = 34 'light turqoise
            Case "OBBRDG", "obbrdg"
                icolor = 35 'light green
            Case "LNCH", "lnch"
                icolor = 53 'brown
            Case "BRK", "brk"
                icolor = 15 'gray-25%
            Case "AV", "av"
                icolor = 10 'green
            Case "AS", "as", "med", "MED"
                icolor = 3 'red
            Case Else
                 'Whatever
            End Select
             
            cell.Interior.ColorIndex = icolor
        Next cell
    End If
     
End Sub

Display More

VBA Noob · Jun 5th 2008

Re: Exceeding Excel's 3 Criteria Limit In Conditional Formatting

You could also remove some of the conditions by using LCase

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Long
    Dim c As Range
        
    If Not Intersect(Target, Range("B2:Q53")) Is Nothing Then
        For Each c In Range("B2:Q53")
            Select Case LCase(c.Value)
            Case "ibbch":     icolor = 36 'light yellow
            Case "obbch":     icolor = 34 'light turqoise
            Case "obbrdg":    icolor = 35 'light green
            Case "lnch":      icolor = 53 'brown
            Case "brk":       icolor = 15 'gray-25%
            Case "av":        icolor = 10 'green
            Case "as", "med": icolor = 3 'red
            Case Else
                 icolor = 0
            End Select
            c.Interior.ColorIndex = icolor
        Next c
    End If
End Sub

Display More

VBA Noob

RNitschke · Jun 5th 2008

Re: Exceeding Excel's 3 Criteria Limit In Conditional Formatting

Thanks so much, you guys are brilliant - it worked perfectly!!!

I need to hit the book store and learn more about VBA within Excel

Exceeding 3 Criteria Limit In Conditional Formatting

Participate now!

Share