+ Reply to Thread
Results 1 to 20 of 20

Conditional Formatting between Two Worksheets ?

  1. #1
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Conditional Formatting between Two Worksheets ?

    Hello,

    Would you please tell me how I can match the figures from one spreadsheet to the other in two different workbooks and if it matches, turn the figure red?

    Would I do this with Conditional Formatting?

    Spreadsheets are attached.
    Last edited by kocumna; 06-12-2018 at 05:26 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Before I open either of those files - are we talking workSHEETS (tabs) or workBOOKS (files) here?
    and yes, CF is probably what you need to use

    Also, please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Conditional Formatting between Two Worksheets ?

    Hi kocumna,


    I thought Conditional Formatting only worked on a single sheet but I'm ready to be wrong on this. There are add-in products that claim they do this. See:


    https://www.extendoffice.com/documen...ing-rules.html
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Marvin, after 2007, it would work directly across sheet (within the same file)
    2007 and before, it would still work across sheets, but you needed to create a named range for the "compare to" cell

    Not sure about across files, I will give that a quick test

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Nope, wont work (directly, at least) across files, will test using named ranges

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Nope, doesnt seem to want to work using named ranges, either

  7. #7
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    Good evening,

    I asked the below question on another thread titled Index, match, len. I am looking to perform this function and once the net savings are found on the Sample CLEAN_BILLING 051818 after Clearing.xlsm spreadsheet that are on the Sample May 2018 HMO Traditional thru 051818 after Clearing - CA Only. XLSX spreadsheet, I would like the figures to turn red so I can see which ones didn't match.

    Please let me know if that makes sense. I am using Excel 2016 working with two separate workbooks.

    1. Index, match, len
    Good afternoon,

    My name is Nadine and I am looking for help on trying to solve get this formula to work or you have a better way to help me get what I need, that would be appreciate!

    I have two workbooks, one being larges and have several rows and columns. One column has Claim numbers in it (there are several rows before that column) and one is a blank column that I need to have net savings automatically put into. There are several columns in between these two.

    My second workbook has the claim numbers that I need to search and find on the first workbook and then return then return the correct net savings that is in a different column on this second workbook. I only can search 9 out of the 11 digits that are in the claim number due to the last two digits are different in each workbook.

    Thank you so much in advance for your help!
    Nadine


    =INDEX('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$G$2:$G$9, MATCH(LEFT('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2,LEN('[CORVEL_CLEAN_BILLING 051818 after Clearing.xlsm]CORVEL_CLEAN_BILLING CA 051818'!$H$2)-2),LEFT(E96:E283,LEN(Claim_number_column: E96:E283)-2),0))


    Hi Nadine,

    Enter the following array formula into C6 cell of in Net saving column using Shift + Ctrl + Enter:

    =IFERROR(INDEX('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$D:$D,MATCH(LEFT(A6,9),LEFT('[Sample CLEAN_BILLING 051818 after Clearing.xlsm]CLEAN_BILLING CA 051818'!$E:$E,9),)),"")

    I would also like to know if this can be done without naming the workbooks/worksheets. Currently I have to change the workbook/worksheet names every time I want to run this formula.

    I am also wondering if this can be set as a macro.

    Thank you so much for your help!
    Nadine
    Last edited by kocumna; 06-12-2018 at 07:45 PM.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional Formatting between Two Worksheets ?

    Quote Originally Posted by kocumna View Post
    I am using Excel 2016
    so show it in your profile I don't know Excel version named: Connecticut

  9. #9
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    I am using Microsoft Office Standard 2016 32-bit. I hope that helps.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional Formatting between Two Worksheets ?

    You didn't read post#8

    ver.jpg

  11. #11
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    Okay. It is fixed now. Thank you for showing me that.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional Formatting between Two Worksheets ?

    You are welcome and thanks. Now will be easier for everyone

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    @ sandy, thanks for the assist

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Conditional Formatting between Two Worksheets ?

    .

  15. #15
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    Good morning!

    Any luck on figuring this one out?

    TIA,
    Nadine

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Nadine, to begin with, this is not the same question as you started the thread with, so it should have been a new thread.
    2nd, if you have already posted this in a different thread, you need to stick with that thread until it has been resolved, not start a new thread or add to an existing thread with that same question.

    Please provide the link to that question, here, and we can continue there.

  17. #17
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    https://www.excelforum.com/excel-for...match-len.html

    My apologies. I did it that way because I wasn't sure how to get the link. Please advise if the above is what you are looking for.

    Thank you!

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional Formatting between Two Worksheets ?

    Yes, that is perfect, thanks

    Seems like you have your answer there anyway?

  19. #19
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    It doesn't tell me how I can turn the claim numbers red when I find a match. This was a new question to this thread.

    It also doesn't tell me how it can be changed so that I don't have to change the worksheet name every time.

    Can you help me with that?
    Last edited by kocumna; 06-14-2018 at 07:32 AM.

  20. #20
    Registered User
    Join Date
    05-25-2018
    Location
    Connecticut
    MS-Off Ver
    2016
    Posts
    67

    Re: Conditional Formatting between Two Worksheets ?

    Good morning!

    Any luck on figuring this one out?

    TIA,
    Nadine

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional formatting across two worksheets
    By mattb1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 08:53 AM
  2. Replies: 2
    Last Post: 08-26-2010, 07:46 AM
  3. conditional formatting between worksheets
    By toclare84 in forum Excel General
    Replies: 5
    Last Post: 07-21-2010, 06:53 AM
  4. Conditional Formatting Over 2 worksheets
    By jason_1 in forum Excel General
    Replies: 20
    Last Post: 11-23-2009, 11:17 PM
  5. Conditional formatting across worksheets
    By Welshwarrior in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-10-2008, 06:10 AM
  6. Conditional Formatting Across Worksheets
    By PsiBorg6 in forum Excel General
    Replies: 4
    Last Post: 10-23-2008, 05:19 PM
  7. conditional formatting between worksheets
    By Nigel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2007, 11:08 PM
  8. [SOLVED] Conditional formatting between worksheets?
    By Workaholic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-13-2006, 06:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1