Conditional Formating Help. Wildcard?

stuartford

Active Member
Joined
Nov 28, 2002
Messages
419
i have conditional formating in a sheet but its not quite right.

I have many different product descriptions some may contain this (NUT) if it does the i would like the cell to turn red.

now doing this with just the word (NUT) in the cell is easy but when its in a string IE
Best MultiPack (NUT)
then im stuck.

Any help would be appreciated

Thanks
Stu
 

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.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,329
Office Version
  1. 365
Platform
  1. Windows
Assume the range of descriptions is in A1:A10

Try this:

1. Select A1:A10
2. Format|Conditional Formatting...|Formula Is =ISNUMBER(FIND("NUT",A1))
3. Click 'Format' and choose colour
4. OK, OK
 

stuartford

Active Member
Joined
Nov 28, 2002
Messages
419
there are many ranges in the sheet that may contain (NUT) i cannot get your formula to work, i have changed the A1 to D11 which is the first cell of about 60 to be checked, would i have to have a different conditional format for each cell refference?


Stu
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,329
Office Version
  1. 365
Platform
  1. Windows
stuartford said:
there are many ranges in the sheet that may contain (NUT) i cannot get your formula to work, i have changed the A1 to D11 which is the first cell of about 60 to be checked, would i have to have a different conditional format for each cell refference?


Stu
Select the first range then hold CTRL while selecting all the other ranges.

Take note of which cell is the Active Cell (look in the Name box just above column A heading). Now do the Conditional Formatting as before and make sure the Active Cell address is the one put in the Conditional Formatting formula (in place of A1 in my previous post).
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218

ADVERTISEMENT

First make formatting in one cell

=ISNUMBER(FIND("NUT",D11))

Then use format painter ( the little yellow painting brush icon ) to copy the format to other cells.

It will change reference according to cells.

Pekka
:eek: :eek:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,329
Office Version
  1. 365
Platform
  1. Windows
Pekkavee said:
First make formatting in one cell

=ISNUMBER(FIND("NUT",D11))

Then use format painter ( the little yellow painting brush icon ) to copy the format to other cells.

It will change reference according to cells.

Pekka
:eek: :eek:
Good idea. And remember that you can double-click the Format Painter and apply the format to all the different ranges and then click the Format Painter again to de-activate it.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218

ADVERTISEMENT

Peter_SSs said:
And remember that you can double-click the Format Painter and apply the format to all the different ranges and then click the Format Painter again to de-activate it.

Nice Peter. :biggrin: :biggrin:
I didn't know the double-click thing

Thanks

Pekka
:eek: :eek:
 

stuartford

Active Member
Joined
Nov 28, 2002
Messages
419
spot on gents
i used the format painter insted of CTRL

keeps the boss off my back for another few hours

thanks
Stu
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,329
Office Version
  1. 365
Platform
  1. Windows
Pekkavee said:
Nice Peter. :biggrin: :biggrin:
I didn't know the double-click thing

Thanks

Pekka
:eek: :eek:
It's funny how you often jump to conclusions. Pekka, for some reason (number of posts I guess) I assumed that you would know about that and I thought that I was providing the hint for Stuart. Anyway, a great little time-saver.

Off the subject of this thread, but the same thing works for drawing objects. If you want to draw several arrows say, double-click the arrow on the drawing toolbar and away you go. Click the arrow again to de-activate.
 

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
Peter_SSs said:
It's funny how you often jump to conclusions. Pekka, for some reason (number of posts I guess) I assumed that you would know about that and I thought that I was providing the hint for Stuart. Anyway, a great little time-saver.

Off the subject of this thread, but the same thing works for drawing objects. If you want to draw several arrows say, double-click the arrow on the drawing toolbar and away you go. Click the arrow again to de-activate.

There is so much for us everybody to learn in Excel. Even if you use Excel in your everyday work and are familiar with it, so allways there is a better way of doing something. Like here the double-click thing. I really didn't know it because I never tried and I am using Format Painter all the time and have been happy with it. :biggrin: :biggrin:

Simple is beautiful and this taught me again a better way.

Thanks Peter.

Pekka
:eek: :eek:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,004
Messages
5,834,833
Members
430,324
Latest member
bosphoruskid

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
Top