Results 1 to 6 of 6

VBA Conditional Formatting - Multiple Criteria

This is a discussion on VBA Conditional Formatting - Multiple Criteria within the Excel Questions forums, part of the Question Forums category; Hello, Using Excel 2003 on XP, I am attempting to add CF to a macro using VBA . I'm very ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    15

    Default VBA Conditional Formatting - Multiple Criteria

    Hello,

    Using Excel 2003 on XP, I am attempting to add CF to a macro using VBA. I'm very new to VBA, so my syntax isn't always clean. I can't find a similar enough reference to what I'm trying to do, so I need help, or I'll pull out the rest of my hair.

    Here is a sample of the code I'm trying to use - pared down to two Cases from six, for visibility:

    Dim fairtype as Range
    Dim rangevar As Range

    Set fairtype = Sheets("Monitor").Range("C1")
    Set rangevar = Sheets("Adv").Range("d2:d600")

    For Each cell In rangevar

    Select Case fairtype

    Case "PRES"
    If (rangevar.Value = "*holiday*") Or (rangevar.Value = "*Yellow*") Or (rangevar.Value = "*pic*") Then
    cell.Interior.ColorIndex = 35
    cell.Interior.Pattern = xlSolid
    End If

    Case "MIL"
    If (rangevar.Value = "*Best*") Or (rangevar.Value = "*Green*") Then
    cell.Interior.ColorIndex = 35
    cell.Interior.Pattern = xlSolid
    End If

    End Select

    Next cell

    I'm getting a "Type Mismatch" error in Case "MIL" (that is what is in the Range when I run the macro, so skipping over Case "PRES" is correct).

    Please let me know how I messed this up.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,458

    Default Re: VBA Conditional Formatting - Multiple Criteria

    This won't work:

    If (rangevar.Value = "*holiday*") Or (rangevar.Value = "*Yellow*") Or (rangevar.Value = "*pic*") Then

    Try:

    If (rangevar.Value Like "*holiday*") Or (rangevar.Value Like "*Yellow*") Or (rangevar.Value Like "*pic*") Then
    Microsoft MVP - Excel

  3. #3
    Board Regular MisterCrash's Avatar
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    279

    Default Re: VBA Conditional Formatting - Multiple Criteria

    You might want to use a Select-Case structure instead of the If-Or structure you have right now.

    You also may want to use the InStr function instead of wildcards. The syntax for that would be:

    InStr([Value to search through], [Value to search for])

    If the second item is found within the first item, InStr will return the position in the first item that it was found. Otherwise, it will return 0.

  4. #4
    New Member
    Join Date
    Sep 2008
    Posts
    15

    Default Re: VBA Conditional Formatting - Multiple Criteria

    Thank you both for your replies!

    Andrew - I tried changing "=" to LIKE, but I still received the "Type mismatch" error.

    MisterCrash - I like the idea of eliminating wildcards, and so I have run the INSTR formula through a macro to get a feel for it. I used the following macro to check the value produced:

    Sub test()

    Dim testval As Integer

    testval = InStr(1, Range("d57"), "e")

    MsgBox testval

    End Sub

    ...which resulted in a value of "2" being displayed. Great! I ran it with "more" instead of "2" - and I verified that "more" is in the cell referenced - it displayed "0" when it should have come back with a value of "13".

    I'm going to try an embedded Select Case instead of an IF/Then. I'll re-post with the results.

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,458

    Default Re: VBA Conditional Formatting - Multiple Criteria

    Yes, it should be:

    Code:
    If (cell.Value = "*holiday*") Or (cell.Value = "*Yellow*") Or (cell.Value = "*pic*") Then
    Microsoft MVP - Excel

  6. #6
    New Member
    Join Date
    Sep 2008
    Posts
    15

    Default Re: VBA Conditional Formatting - Multiple Criteria

    BINGO! That did it. Now I see how I messed the syntax up - I was looking to use the "cell", but referred back to the variable instead. That's why the error.

    Thank you for all of your help!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com