conditional formatting based on part of a cell content

[steve hill]

Hi
I have a column that contains a goods in number 5 characters followed by a space then GII or GIP or a bin location ie A100, I want to use conditional formatting to highlight cells containing GII one colour and GIP another colour the remainder stay normal

Thanhs for your time

Steve

 

[Norie]

Steve

Try this:

Select the cells your interested in, let's say B2:B20.

Goto conditional formatting and select to create a new rule based on a formula.

Enter this formula for GIP:

=SEARCH("GIP",B2)

and format as required.

Then add another rule for GII:

=SEARCH("GII",B2)

Note both of these are case insensitive, if it needs to be case sensitive use FIND.

Also, if you need to colour a whole range row, select the range and instead of B2 use $B2 in the formulas.

Obviously change the cell references for your own set-up.

 

[steve hill]

Steve

Try this:

Select the cells your interested in, let's say B2:B20.

Goto conditional formatting and select to create a new rule based on a formula.

Enter this formula for GIP:

=SEARCH("GIP",B2)

and format as required.

Then add another rule for GII:

=SEARCH("GII",B2)

Note both of these are case insensitive, if it needs to be case sensitive use FIND.

Also, if you need to colour a whole range row, select the range and instead of B2 use $B2 in the formulas.

Obviously change the cell references for your own set-up.

HI
I have tried both but seem to be geeting other cells highlighlighted and even some blank ones the data in the cells is put there using vlookup with the following formula will this effect the formating
=IF(ISERROR(VLOOKUP(LEFT(A3,5),'stock location'!$E:$F,2,FALSE)),"",VLOOKUP(LEFT(A3,5),'stock location'!$E:$F,2,FALSE))

thanks steve

 

[Norie]

Steve

No a formula shouldn't affect it.

If you are getting incorrect cells highlighted it could be because they do contain GII or GIP, but not the GII or GIP you are looking for.

How exactly did you enter the formula and which range is it meant for?

 

[steve hill]

Steve

No a formula shouldn't affect it.

If you are getting incorrect cells highlighted it could be because they do contain GII or GIP, but not the GII or GIP you are looking for.

How exactly did you enter the formula and which range is it meant for?

hi
I selected the cloumn "J" entered into conditional format by formula changed B2 to J2
some cell that are highlighted
27662 ENGI
27663 GII
27664 GII
27665 GII
27681 AR19
and a blank cell
thanks
Steve

 

[Norie]

Steve

Shouldn't some of those be formatted?

Did you select the entire column?

If you did then the reference in the formula should probably be J1, not J2 or B2.

Basically the row needs to be the first row for the reference needs to be the first row of the data you want to format/use for the format.

One other thing to check is if you missed out the equals sign, which is a mistake I made myself.

Without it Excel will evaluate the what you've entered as true no matter what, so the formatting will be applied.

 

[steve hill]

Steve

Shouldn't some of those be formatted?

Did you select the entire column?

If you did then the reference in the formula should probably be J1, not J2 or B2.

Basically the row needs to be the first row for the reference needs to be the first row of the data you want to format/use for the format.

One other thing to check is if you missed out the equals sign, which is a mistake I made myself.

Without it Excel will evaluate the what you've entered as true no matter what, so the formatting will be applied.

hi
I had selected the column and entered the cell were the data started changing it to J1 has worked
Thanks

Steve