conditional formatting based on part of a cell content

steve hill

Board Regular
Joined
Jul 11, 2006
Messages
155
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 11, 2006
Messages
155
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 11, 2006
Messages
155

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.:oops:

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

steve hill

Board Regular
Joined
Jul 11, 2006
Messages
155
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.:oops:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,858
Messages
5,598,487
Members
414,243
Latest member
Shockpulsar

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