Conditional format question

[Mitel]

If anyone can help out, it would be greatly appreciated.

Data: I have a list of text in column A (A1:A99) and then in column's D3:J3, I input any selection of the Data from column A.
I need the data in column A to change color (text or cell) if that data is not represented in D3:J3.

I am using Office 2007, WinXP if that makes a differance.

 

[n0gel]

Try something like this

Countif(D3:J3,A1)=0

Also, most people using Excel 2007 need to have the hotfix described in Article 973823 done to their computer. It's a quick registry hack and it's done. This fixes a problem with copying & pasting cells with CF.

 

[Mitel]

I tried adding that as a new conditional formatting rule (to cell A1 and chose a color to change both the txt and cell to) but it didn't seem to work.

 

[n0gel]

Is D3:J3 actually the range, or does it go down to say row 100 like D3:J100?

Also, you might have better luck with a named range instead.

 

[vavs]

This works for a range of cells:

http://spreadsheetpage.com/index.php/tip/comparing_two_lists_with_conditional_formatting/

Set your D3:J3 range up in name manager as "match"

Highlight cells A1:A99 and select conditional formatting

Create a new rule and select use a formula

Enter the formula =countif(match,a1)=0 and set your formatting.
Any cell that does not have a match in D3:J3 will be formatted.

If you want to format the cells that do match change the =0 in the formula to =1

Good luck

 

[Mitel]

Thanks heaps guys!