Conditional Formatting - highlight a cell if it does not equal multiple text values

[Deman]

from cell C1 to cell C39 i have a list of roles (which i hide), below other people sign in and in colum C is where they enter there role, sometimes people enter their role in incorrectly, If this occurs i want the cell to highlist red, ive checked the Conditional Formatting > manage rules > new rule > format cells that contain,

I have cell value not equal to, but dont know what to enter in that box to do what i want,

The contents of the cells C1:C39 are all text, i would also want the cells not to highllght if it is blank

thanks in advance for any feedback

 

[mvptomlinson]

Instead of 'Format cells that contain' change to 'use formula to determine....' and try:

=NOT(ISNUMBER(MATCH(C40,$C$1:$C$39,0)))

 

[Deman]

thank you that almost operates perfectly, the only issue is the cells are now filling red when they are blank, is there a way to only apply the condition when there is something in the cell

 

[mvptomlinson]

Perhaps:

=AND(C40<>"",NOT(ISNUMBER(MATCH(C40,$C$1:$C$39,0))))

 

[Deman]

=IF(C40="",FALSE,(NOT(ISNUMBER(MATCH(C40,$C$1:$C$39,0)))))

managed to figure it out, thanks so much for your help, wish i would of posted this 2 hours ago

 

[mvptomlinson]

You don't need to use an IF function in conditional formatting. The function you choose will (should) result in either TRUE or FALSE. When it's TRUE, the conditional format will be applied.

You're welcome.

 

[Aneirin]

Hey, though I would ressurect a thread rathe rthan start a new one.

I am trying to do something similar.

I have the following coloumns

J N

A 23
C 34
D 10
D 40
B 30
A 40
C 10

J is a drop down list (A, B, C and D)

I have given the following conditional format rules:-

Highlight is N>33
Font change to white if N=error

Now, what I want to do is get it so that N is font changed to white if J=A,B or D (though happy to make three different formulaes if that is easier)

I have tried:-

=$J$2<>A

But that doesn't format anything.

Also tried

=$J$2<>J1

But that just formats everything white

 

[Aneirin]

Mistake made, $J$2 meant to read $J$J, same problem still occurs

I have tried:-

=$J$J<>A

But that doesn't format anything.

Also tried

=$J$J<>J1

But that just formats everything white

 

[Deman]

wow this one takes me back, try =$J$J<>"A"

 

[Aneirin]

wow this one takes me back, try =$J$J<>"A"

Thanks, just worked it out.

Instead of doing $j$j I just did J1<>"A"

Think it was doing the whole row at once. Select the whole row, do the first one and excel auto updates it for the next ones changing J1 to J2, then J3 and so on and so forth