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

M

mvptomlinson

Well-known Member

Joined

Mar 10, 2008

Messages

2,638

• Jul 14, 2011

• #2

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

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

 

Upvote 0

D

Deman

Board Regular

Joined

Jul 14, 2011

Messages

91

• Jul 14, 2011

• #3

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

 

Upvote 0

M

mvptomlinson

Well-known Member

Joined

Mar 10, 2008

Messages

2,638

• Jul 15, 2011

• #4

Perhaps:

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

 

Upvote 0

D

Deman

Board Regular

Joined

Jul 14, 2011

Messages

91

• Jul 15, 2011

• #5

=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

 

Upvote 0

M

mvptomlinson

Well-known Member

Joined

Mar 10, 2008

Messages

2,638

• Jul 15, 2011

• #6

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.

 

Upvote 0

A

Aneirin

New Member

Joined

Aug 28, 2014

Messages

13

• Aug 4, 2015

• #7

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

 

Upvote 0

A

Aneirin

New Member

Joined

Aug 28, 2014

Messages

13

• Aug 4, 2015

• #8

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

 

Upvote 0

D

Deman

Board Regular

Joined

Jul 14, 2011

Messages

91

• Aug 4, 2015

• #9

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

 

Upvote 0

A

Aneirin

New Member

Joined

Aug 28, 2014

Messages

13

• Aug 4, 2015

• #10

Deman said:

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

Click to expand...

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

 

Upvote 0