Greetings, Gurus.
First - It appears the search function isn't working. I tried 3 times and just go hng up on "Waiting on Excel Forum...."
How would I conditionally format a cell so that if it finds the number entered in that cell in another worksheet it will highlight the cell yellow.
The name of the other worksheet is "NPA Log" if that is needed.
Thanks in advance for any help you can offer.
Have a good one.
Last edited by [email protected]; 01-20-2017 at 08:39 PM.
When looking to find the number in "another worksheet", do you need to check only a specific cell in that sheet, or a range in that sheet, or the whole sheet?
Please consider uploading a sample workbook with 2 sheets (one with the cells you are working on, the other being the sheet where you are looking for the match). To upload it, click the Go Advanced button on your post, and on the advanced posting page click Manage Attachments.
I am trying to highlight any cells entered in Column E of the "NPA" worksheet yellow if they are found anywhere in Column F of the "NPA Log" worksheet.
Sample attached.
What version of Excel does this have to work in?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hopefully all of them, but I am using Office 2010.
And by the way, I am terrified of volatile functions or array formulas.
Use the following formula to determine which cells get the yellow format: =IF(NOT(ISBLANK(E2)),ISNUMBER(MATCH(E2,'NPA Log'!$F:$F,0)),FALSE)
This is for conditional formatting in cell E2: once you've got it in there, just apply the conditional formatting to as many cells as you need in Col E. Your example is attached with conditional formatting implemented down as far as E17.
If you want it to work in "all of them" then you'll have to create a named range.
Goto the Formulas tab>Define Name
Name: KeyNum (or whatever you want to call it)
Refers to: ='NPA Log'!$F$2:$F$17
OK
Then, setup the conditional formatting.
Let's assume you want to format the range E2:E10 on the NPA sheet.
Select the ENTIRE range E2:E10 starting from cell E2.
Cell E2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.
Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format
Enter this formula in the box below:
=MATCH(E2,KeyNum,0)
Click the Format button
Select the desired style(s)
OK out
Brilliant, Guys. Thanks. I went with ianpage's solution for now, but I will keep Tony's in my hip pocket if anyone complains their workbook isn't working correctly.
This one is SOLVED. Thanks again, and have a great weekend!
You're welcome. Thanks for the feedback!
Ditto! Happy to help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks