Conditional Formatting - Multiple wildcard criteria

robtyketto

Board Regular
Joined
Oct 25, 2006
Messages
111
Greetings,

I have a column with values and I change the fill using a set of conditional formatting rules with the criteria below (well as much as I can remember from work, so these are not the exact values but the same principle)
Excel Formula:
=COUNTIF(A1:A100, "*AA*")
=COUNTIF(A1:A100, "*AB*")
=COUNTIF(A1:A100, "*AC*")
This works fine, however instead of having xx rules I was trying to see if a single function would search a column for a number of wildcard values and highlight them.

I cant use arrays, so they are out of the picture.
I thought using COUNTIFS to specificy multiple criteria e.g. =COUNTIFS(A1:A100,"*AA*",A1:A100,"*AB*") this didn't work.

Can I have one conditional formatting rule to trigger where text is like "*AA*", "*AB*", "*AC*" etc..,?

Not posted here in ages, brings back memories when I used to post more frequently when first started to use excel in the mid-late 90's!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
This should do the job.
Excel Formula:
=OR(COUNTIFS(A1:A1,"*AB*"),COUNTIFS(A1:A1,"*AC*"),COUNTIFS(A1:A1,"*AA*"))
 

Attachments

  • 1675275557240.png
    1675275557240.png
    21.8 KB · Views: 7
Upvote 1
Solution
Hi Rob,

For conditional formatting using a variety of criteria, I prefer to use a Defined Name (range) as it offers greater flexibility.

I use this in one of my Trackers that help me identify a books status. However, I created a SAMPLE using the following data:

Book1
ABCD
1DataTestCriteria
2aaa1aa
3aab2ab
4aac2ac
5aad1
6slab1
7department0
8ability1
9queen0
10category0
11space1
12bath0
13requirement0
14region0
15signature0
16replacement1
17love0
18relationship0
19technology0
20aardvark1
Sheet1
Cell Formulas
RangeFormula
B2:B20B2=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))
Named Ranges
NameRefers ToCells
cfCriteria=Sheet1!$D$2:$D$4B2:B20
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A20Expression=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))textNO

The Data column holds the data you want to conditionally format.
The Test column is being used just to show us how that our code is identifying the data based on the criteria correctly before using it with CF. It also shows you how many times that data meets your criteria which is where the SUM comes into play. This column can be removed after testing.
The Criteria column shows the criteria you want to use for your Conditional Formatting. The use of the Wildcards will be used within the CF Formula later.

You can place your criteria list on another Sheet (even hidden if needed). In order for your Criteria to grow without updating your CF Rule, you'll want to Format that Criteria list into a Table. However, you cannot reference a Table with CF. This is why I have the same range as a Named Range and a Table. I prefixed cf to the name so I know that range is only used for Conditional Formatting.

1675278238015.png

Now if you need to add some new criteria that fits the same wild card format you're using here, then just add it to the Table and your data will be formatted with this new value. If you remove a Criteria from the list, then those cells will no longer be highlighted.

To test this, I added "ee" to the Criteria Table and the word queen was automatically highlighted.

1675278430805.png

That's because the Conditional Formatting rule was updated automatically using that Named Range:
You can see that the range now goes down to Row 5 to account for that new criterion that I added.

1675278518823.png

Book1
ABCD
1DataTestCriteria
2aaa1aa
3aab2ab
4aac2ac
5aad1ee
6slab1
7department0
8ability1
9queen1
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))
Named Ranges
NameRefers ToCells
cfCriteria=Sheet1!$D$2:$D$5B2:B9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A20Expression=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))textNO


You can check out this article that goes over a few other ways...
 
Last edited:
Upvote 0
This should do the job.
Excel Formula:
=OR(COUNTIFS(A1:A1,"*AB*"),COUNTIFS(A1:A1,"*AC*"),COUNTIFS(A1:A1,"*AA*"))
Thanks, I did use the OR as part of experimenting without the COUNTIFS , perfect simple solution.
Just tested it, all good. Was a little confused as used COUNTIFS with all the criteria is the same set of brackets, useful to know this.
 
Upvote 0
Hi Rob,

For conditional formatting using a variety of criteria, I prefer to use a Defined Name (range) as it offers greater flexibility.

I use this in one of my Trackers that help me identify a books status. However, I created a SAMPLE using the following data:

Book1
ABCD
1DataTestCriteria
2aaa1aa
3aab2ab
4aac2ac
5aad1
6slab1
7department0
8ability1
9queen0
10category0
11space1
12bath0
13requirement0
14region0
15signature0
16replacement1
17love0
18relationship0
19technology0
20aardvark1
Sheet1
Cell Formulas
RangeFormula
B2:B20B2=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))
Named Ranges
NameRefers ToCells
cfCriteria=Sheet1!$D$2:$D$4B2:B20
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A20Expression=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))textNO

The Data column holds the data you want to conditionally format.
The Test column is being used just to show us how that our code is identifying the data based on the criteria correctly before using it with CF. It also shows you how many times that data meets your criteria which is where the SUM comes into play. This column can be removed after testing.
The Criteria column shows the criteria you want to use for your Conditional Formatting. The use of the Wildcards will be used within the CF Formula later.

You can place your criteria list on another Sheet (even hidden if needed). In order for your Criteria to grow without updating your CF Rule, you'll want to Format that Criteria list into a Table. However, you cannot reference a Table with CF. This is why I have the same range as a Named Range and a Table. I prefixed cf to the name so I know that range is only used for Conditional Formatting.


Now if you need to add some new criteria that fits the same wild card format you're using here, then just add it to the Table and your data will be formatted with this new value. If you remove a Criteria from the list, then those cells will no longer be highlighted.

To test this, I added "ee" to the Criteria Table and the word queen was automatically highlighted.


That's because the Conditional Formatting rule was updated automatically using that Named Range:
You can see that the range now goes down to Row 5 to account for that new criterion that I added.


Book1
ABCD
1DataTestCriteria
2aaa1aa
3aab2ab
4aac2ac
5aad1ee
6slab1
7department0
8ability1
9queen1
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))
Named Ranges
NameRefers ToCells
cfCriteria=Sheet1!$D$2:$D$5B2:B9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A20Expression=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))textNO


You can check out this article that goes over a few other ways...
Thanks, very useful to know another method.
Will try this method sometime soon I'm sure, I'm typically in T-SQL but use Excel with end users occassionaly to explain data.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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