Conditional formatting with an IF function

[Balmer07]

Hi,

I am currently working on a spreadsheet which I need to conditional format for the user.

In column 'B' I have either "inbound" or "outbound" and in column 'C' I have Product codes (E.g Part 1, Part 2, Part 3)

I am not sure if conditional formatting can do what I need and it may require VBA but hoping someone can advise me on here first before I start into VBA

Basically if column 'B' contains "outbound" I want to conditional format to bypass the formatting. However if Column 'B' contains "inbound" I want the Part code to be highlighted in Column 'C'.

I hope this makes sense.

So far if i use the below formula the product codes highlight for both inbound and outbound.

=OR(C1="Part 1",C1="Part 2",C1="Part 3")

Many thanks in advance!

 

[becci.gott]

Are you formatting the different parts the same? If not, just have the criteria as =B1="Inbound" and apply the formatting to column C.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Say your data are in B1:C10

Select C1:C10 being C1 the active cell (the one not shaded after the selection)
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
insert this formula
=B1="inbound"
Format button and pick the format you want
Ok, Ok

Hope this helps

M.

 

[Balmer07]

No, I should have said that, I only need part to be formatted out of the 3 parts

Example:

Inbound

 

[Balmer07]

Sorry I posted too quickly on my last post,

I only need specific parts formatted so say it was for Part 1 is the only part I needed formatted

Example:

Inbound Part 1 (need formatted)
Outbound Part 1 (no formatting)
Inbound Part 2 (no formatting)
Outbound Part 2 (no Formatting)
Inbound Part 3 (no formatting)
Outbound Part 3 (no formatting)

 

[Marcelo Branco]

Try this formula in CF

=AND(C1="Part 1",B1="Inbound")
pick the format you want

M.

 

[becci.gott]

Same as Marcelo has said above then, but with a slight change to the formula:

=and(B1="inbound",C1="Part 1")

And apply it to column C.

 

[becci.gott]

Great minds think alike.

 

[Balmer07]

Hey guys,

I realised I made a slight mistake in my cell selection, and re-looked at my formula, I was able to make a small adjustment and came up with the below which works perfectly,

=IF(B1="inbound",OR(C1="Part 1",C1="Part 2")

I have a list of roughly 400 parts but only need 6 parts to be formatted using this formatting, there are maybe 20-30 rows with these parts so this is why I needed this type of formatting.

Thanks for all your suggestions and help

Steven

 

[Marcelo Branco]

Hey guys,

I realised I made a slight mistake in my cell selection, and re-looked at my formula, I was able to make a small adjustment and came up with the below which works perfectly,

=IF(B1="inbound",OR(C1="Part 1",C1="Part 2")

I have a list of roughly 400 parts but only need 6 parts to be formatted using this formatting, there are maybe 20-30 rows with these parts so this is why I needed this type of formatting.

Thanks for all your suggestions and help

Steven

IF is not necessary. Try
=AND(B1="inbound",OR(C1="Part 1",C1="Part 2"))

A better solution would be to create a range containing the parts that should be highlighted, name it, say, PartList and use this formula in CF
=AND(B1="Inbound", ISNUMBER(MATCH(C1,PartList,0)))

M.