Conditional formatting in Excel...

I've got an Excel 2013 doc, that I need to color all the rows yellow when a certain phone number appears in one of the cells.
If it matters, there are 7 columns & phone numbers will appear in 1 of 2 cells on any given row.
Can this ne easily done?
Thanks!
LVL 1
gromackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Yes easily done with conditional formatting.

If you can upload a sample file we can apply it for you.
1
Rob HensonFinance AnalystCommented:
See attached sample file.

The Conditional Format rule is based on the following formula:
=OR($G2=$K$1,$H2=$K$1)

Note the use of $ to ensure that all cells are looking at the right columns.

This is based on a specific phone number which is effectively a string of text due to the space between area code and number. You could adjust the formula to allow for other conditions eg starts with 0800

=OR(LEFT($G2,4)="0800",LEFT($H2,4)="0800")
Conditional-Format.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gromackAuthor Commented:
In the attached, if I wanted to fill the whole row yellow when any of the given cells contains 2815551212
Book1.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
See attached.

I haven't done whole row as you mentioned you have 7 columns so only did as far as column G.

Formula for CF:
=OR($A1=2815551212,$B1=2815551212)
Conditional-Format2.xlsx
0
gromackAuthor Commented:
Thanks, again!
0
Rob HensonFinance AnalystCommented:
No probs
0
gromackAuthor Commented:
Sorry, just one more stupid question. Once you have rule in place, how do you get it to run?

I have 'Format values where this formula is true:' set at =OR($B1=2815551212,$C1=2815551212) (and yes, I have the actual number I'm looking for in place of the 5551212) since my numbers are in either column B or C. Its set to applies to =$A$1:$G$14356 since those are the cells in use - what am I missing?
0
Rob HensonFinance AnalystCommented:
When setting up the CF, did you specify the format, ie yellow fill? I assume so.

If that's the case, I can only assume that the number isn't being recognised. With one of the cells that you know contains the phone number, do a simple comparison in a spare cell. For example if you can see that B3 contains the number use this in a separate cell:

=B3=5551212  (replacing with proper number obviously)

You will get either TRUE or FALSE. If FALSE then it indicates that the number is not being recognised and I suspect it is text that looks like a number, like in my first example. If that's the case, try amending that simple comparison to:

=B3="5551212"   ie enclose the number in double quotes. If that now gives TRUE, then you will need to amend the CF formula to enclose the number in double quotes:

=OR($B1="2815551212",$C1="2815551212")
0
gromackAuthor Commented:
D'OH!
there was a 1 before number that wasn't in my formula!
Thanks, again - its working now!
0
Rob HensonFinance AnalystCommented:
Well, it is Friday afternoon!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.