Conditional formatting

[Guest]

I am trying to flag part numbers when they are entered in a certain column by
making it bold red. These part numbers contain letters and numbers. We have
an extensive list of these part numbers on another sheet that I would like
for the program to review each time a part number is entered. I cannot use
conditional formatting on the tool bar because it only allows for 3
conditions. Any suggestions. I know almost nothing about macros.

 

[Norman Jones]

Hi Mustang,

See xlDynamic's CFPlus Add-in which may be
downloaded, free of charge, at:

CFPlus - Extended Conditional Formatter
http://www.xldynamic.com/source/xld.CFPlus.Download.html

 

[Bernard Liengme]

The problem seems not so much that you have more than 3 conditions (you seem
to have only 1: that the cell entry is matched by one in the list), but that
the list is on another sheet and Conditional Formatting cannot cope with
this.
Why not use an otherwise free space on the 'main' worksheet to house either
the list or a reference to it such as =Sheet2!A1 (copy this down the column
as far as is needed). In my example I put the list in K
Then use a condiontional format with Formula Is COUNTIF($K$1:$K$100,$A$1)
with bold red font
Note that K (or wherever you choose can be hidden)
best wishes

 

[Guest]

Mr. Liengme;

I moved the list to column K in the sheet I wanted to format then
selected the entire column I would enter the data, column c, and selected
conditional format, if formula COUNTIF($K$1:$K$100,$A$1), selected the bold
red and selected ok. I then entered the exact part numbers into column c,
but it would not change the formatting. I must be doing something wrong. If
you could help me again, it would be greatly appreciated.

 

[Guest]

Mr. Jones;

Thank you for the help, but I am using this formula for a project at
work, which will not allow us to use add ins. However, I do appreciate the
time and effort you have provided.

 

[Tim Williams]

This will change the cell format when the content is *not* in the list of
part numbers
(a named range, which does not need to be on the same sheet).

=ISERROR(MATCH(B5,LIST1,0))

Tim

 

[Gord Dibben]

If your data entry is in column C select column C and Format>CF.

Change the $A$1 to $C1

=COUNTIF($K$1:$K$100,$C1)............note the = sign and the exact placement of
the $ sign in $C1


Gord Dibben MS Excel MVP

 

[Guest]

Wow, it worked great!!! Thank you very much for your help!

 

[Guest]

Thank you, I added your format along with Gords, and they both accomplished
what I needed. How does your formula know where my list is located?

 

[Tim Williams]

"LIST1" is a named range: select the cells containing the list and type the
name in the box at top left (don't forget to press Enter to commit the
name).

Tim