Conditional Formating Help. Wildcard?

[stuartford]

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

 

[Peter_SSs]

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]

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]

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]

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

 

[Peter_SSs]

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

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]

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.
I didn't know the double-click thing

Thanks

Pekka

 

[stuartford]

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]

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

Thanks

Pekka

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]

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.

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

Thanks Peter.

Pekka