Another Conditional Formatting Formula needed?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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"
 
Are you sure that works?

If yes, then good.

If no, you may want to restate your requirements.
 
Back
Top