In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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

Hi Dave:

Yes I did mean that I want A4:A3500 to change colors if that company name
appears in any cell in P4:P3500. 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 P4:P3500. Any other suggestion would be
much appreciated.

Dan

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



"Dave Peterson" wrote:

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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

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

=P4:P367="Amethyst Counseling"

"Dave Peterson" wrote:

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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

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"

"Dan the Man" wrote:

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

=P4:P367="Amethyst Counseling"

"Dave Peterson" wrote:

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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

Are you sure that works?

If yes, then good.

If no, you may want to restate your requirements.

Dan the Man wrote:

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

=P4:P367="Amethyst Counseling"

"Dave Peterson" wrote:

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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


--

Dave Peterson

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

Dan

"Dave Peterson" wrote:

Are you sure that works?

If yes, then good.

If no, you may want to restate your requirements.

Dan the Man wrote:

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

=P4:P367="Amethyst Counseling"

"Dave Peterson" wrote:

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 P4:P3500, you could:

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



Dan the Man wrote:

In Column A I have a list of last names (A4:A3500), and in Column P I have a
list of companies (P4:P3500). 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 (P4:P3500). 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


--

Dave Peterson