Another Conditional Formatting Formula needed?

[Dan the Man]

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P43500). I'd like to use conditional formatting to
change the color of the last name in Column A (A4:A3500) to blue, when a
specific company name appears in Column P (P43500). I'll assume that this
is an Array formula to use with conditional formatting, but couldn't get it.
I tried something like what you see below, but it didn't work:

=SUMPRODUCT(--($A$4:$A$3500=$A4),--($B$4:$B$3500=$B4))=Amethyst Counseling


Help on this one would be much appreciated?

Dan

 

[Dave Peterson]

Does this mean that the company name has to be on the same row as the last name?

If yes, you could:
select A4:a3500
Formula is: =p4="Amethyst Counseling"

If you mean that you want A4 to change colors if that company name appears in
any cell in P43500, you could:

Select A4:A3500
formula is: =countif($p$4:$p$3500,"Amethyst Counseling")>0

 

[Dan the Man]

Hi Dave:

Yes I did mean that I want A4:A3500 to change colors if that company name
appears in any cell in P43500. I tried your suggestion (repasted below),
but it changed all of the names in A4:A3500 to the same color/font,
regardless of the company name in P43500. Any other suggestion would be
much appreciated.

Dan

Select A4:A3500
formula is: =countif($p$4:$p$3500,"Amethyst Counseling")>0

 

[Dan the Man]

I got it. I made a slight alteration to your formula:

=P4367="Amethyst Counseling"

 

[daddylonglegs]

Possibly that method works but it wouldn't be the correct way to do it, each
cell is really only evaluating the first cell in the range so just use

=P4="Amethyst Conseling"

 

[Dave Peterson]

Are you sure that works?

If yes, then good.

If no, you may want to restate your requirements.

 

[Dan the Man]

Works like a charm................I'm OCD about testing, and I checked
several cells!

Dan