Highlight automatically anything that is other than required
 

Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan

Select the cells in which you want the highlighting
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=AND(A5<"",COUNTIF($A$1:A$4,A5)=0)
Click the Format button, and select the options you want
Click OK, click OK

Adnan wrote:
Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

I was afraid I did not make myself clear, what I'm looking for is that every
time I enter anyother name in this row beside those 4 listed names, highlight
it/them, even if I delete the first name (Adnan) and insert a different one
(such as Smith) auto-highlight somehow Smith but do not highlight those
current listed names.

If it still doesnt make sense I send you a sample of a Spread Sheet,
thatll describe better.

Thanks again for help ,
Adnan



"Debra Dalgleish" wrote:

Select the cells in which you want the highlighting
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=AND(A5<"",COUNTIF($A$1:A$4,A5)=0)
Click the Format button, and select the options you want
Click OK, click OK

Adnan wrote:
Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,
Heres a better explanation, after you click here
http://adnani.8m.com/photo4.html choose the second picture and for details
click on picture itself, which will lead to you your nice website, MS Excel
Attachment you worked on. Then there are details...

Thank you,
Adnan


"Debra Dalgleish" wrote:

Select the cells in which you want the highlighting
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=AND(A5<"",COUNTIF($A$1:A$4,A5)=0)
Click the Format button, and select the options you want
Click OK, click OK

Adnan wrote:
Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

If you want highlighting to remain even if the names in the list change,
you could use programming to colour the cells. Conditional formatting
would change if the list of four names changed.

Adnan wrote:
Debra,

I was afraid I did not make myself clear, what I'm looking for is that every
time I enter anyother name in this row beside those 4 listed names, highlight
it/them, even if I delete the first name (Adnan) and insert a different one
(such as Smith) auto-highlight somehow Smith but do not highlight those
current listed names.

If it still doesnt make sense I send you a sample of a Spread Sheet,
thatll describe better.

Thanks again for help ,
Adnan



"Debra Dalgleish" wrote:


Select the cells in which you want the highlighting
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=AND(A5<"",COUNTIF($A$1:A$4,A5)=0)
Click the Format button, and select the options you want
Click OK, click OK

Adnan wrote:

Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes! it worked... Thank you Debra :-) (Your website is great)

Adnan

"Debra Dalgleish" wrote:

If you want highlighting to remain even if the names in the list change,
you could use programming to colour the cells. Conditional formatting
would change if the list of four names changed.

Adnan wrote:
Debra,

I was afraid I did not make myself clear, what I'm looking for is that every
time I enter anyother name in this row beside those 4 listed names, highlight
it/them, even if I delete the first name (Adnan) and insert a different one
(such as Smith) auto-highlight somehow Smith but do not highlight those
current listed names.

If it still doesnt make sense I send you a sample of a Spread Sheet,
thatll describe better.

Thanks again for help ,
Adnan



"Debra Dalgleish" wrote:


Select the cells in which you want the highlighting
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell, e.g.:
=AND(A5<"",COUNTIF($A$1:A$4,A5)=0)
Click the Format button, and select the options you want
Click OK, click OK

Adnan wrote:

Hi,

I have a range of only names I wanted entered, anything that is not one of
these names I want to be highlighted somehow, maybe with IF Formula or
Conditional Formatting etc€¦

Here's an example:

A B
1 Adnan
2 John
3 Virgil
4 Jerry
5
6

So, anything entered in A5, A6 and so on, other then those four above listed
names to be automatically highlighted.

Your reply on this problem is greatly appreciated,

Best regards,
Adnan




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html