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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
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
47,486
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
47,486
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
47,486
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,389
Messages
5,571,850
Members
412,421
Latest member
Rimo86
Top