CF Red if cell value omitted

[WorkingMan]

Hello,

I tried searching for CF if value omitted but it didn't return any posts. I'm not sure if i know the right words to search.


I'm trying ot find info on how to change a cell red via CF if a particular value is not found in a range of cells.


Can anyone point me in the right direction?


Thanks

Working Man

 

[Hui]

Working Man


Assuming your CF Range has a Top Left corner of A2

Your formula should be like

=A2<>Value


or

If you are looking for a value in a Range it should be like:

=MATCH(Value, Range, 0)=0

Can you give us some more details or post the file ?

 

[WorkingMan]

Thank you Hui,


I'm haveing trouble posting a file.


Hope this makes sense.


If cell, B2 is "F2" and B3 is "ps1" and B4 is "er1" and B5 is "er2" and B6 is "F1", I want to turn cell, B1 Red if "F2" or "er3" or "ps2" are not found withing the range B2:B25.

Thanks again

 

[Hui]

I can't answer your post now

But to post a file have a read of:

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Montrey]

Here ya go. this works


=IF(NOT(OR(COUNTIF(B2:B6,"f2"),COUNTIF(B2:B6,"er3"),COUNTIF(B2:B6,"ps2"))),TRUE,FALSE)

 

[WorkingMan]

thank you Montrey!!

 

[Montrey]

Hey No prob. Thanks for the feedback. Some users will say you dont need the If statement but it helps users who are learning excel to understand the logic of the formula.