Excel 2002 Condition Formating

[*]

I am having a problem with conditional formating.
I need to change the color of the numeric value to "red" if it is not equal to 490 or 500 or 510 or 520. Have tried various methods but not successful. All numbers in "each" cell turn red whether they are equal to the above values or not.
Have referenced many excel websites for example/tutorial.
I need more than 3 conditions though I wonder if it cpould be accomplished with one condition and a formula. I don't want to reference other cells but will if I have to.
TIA
Dave

 

[Debra Dalgleish]

List the valid entries on the worksheet, e.g. in cells F1:F4

Select cell B2
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
Enter a formula that refers to you list of numbers, and the active cell:
=COUNTIF($F$1:F$4,B2)=0
Click the Format button, and on the Font tab, select the red font colour
Click OK, click OK

 

[Ragdyer]

To keep the formatting rules entirely within the Conditional Format formula,
where the cells to be formatted are A1:A10,

Select A1 to A10, with A1 the cell in focus (white), then:

<Format> <ConditionalFormat>,

Click "Formula Is", and enter this:

=AND(A1<>490,A1<>500,A1<>510,A1<>520)

Then click the "Format" button, and you can choose "Bold" and "Red",
Then <OK> <OK>,
And you should have what you're looking for.

For more then 3 conditions, check out John McGimpsey's web site:

www.mcgimpsey.com/excel/condit­ional6.html

Or an XL add-in from Bob Philips and the late Frank Kabel:

www.xldynamic.com/source/xld.C­FPlus.Downlaod.html