VBA Conditional Formatting - Multiple Criteria

kbruner

New Member
Joined
Sep 8, 2008
Messages
16
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Yes, it should be:

Code:
If (cell.Value = "*holiday*") Or (cell.Value = "*Yellow*") Or (cell.Value = "*pic*") Then
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top