+ Reply to Thread
Results 1 to 12 of 12

How to compare cells and format conditionally over a whole sheet

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    How to compare cells and format conditionally over a whole sheet

    I wish to compare two sheets [Sheet1 and Sheet2] within one workbook and format the second sheet conditionally, based on an exact match to the first sheet.

    Cell by cell, I can add the specific conditional formatting rule to each cell but this is tedious and its hard to see where I have applied the conditional format and where not.

    So I tried to construct a macro to apply to a range of cells.

    I tried to assign the logical result of the compare test to a cell outside of the range[lets say on Sheet2, A1] , and then format the range cells based on that logical value. I think my error is in conditional format criterion but I am a bit stuck.

    Many thanks in advance for your help!
    HilaryP


    Please Login or Register  to view this content.
    Last edited by HilaryP; 05-30-2014 at 05:36 AM.

  2. #2
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    oops! Thanks Fotis

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to compare cells and format conditionally over a whole sheet

    Perhaps you need to re-think about Conditional Formatting. You can use it to your whole range by 1 click. Take a look to the link of how to yse CF.

    How can you use Conditional Formating.

    --In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    Thank you :-)
    I have studied that link and I will keep trying to get it to work. Its the relative reference to update for each cell that I haven't worked out yet.
    I can get it to work by row but not by cell (yet)

    thanks again!

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to compare cells and format conditionally over a whole sheet

    if you'll need help on this, pls upload a small sample workbook.

  6. #6
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    Thanks :-) here you go
    you will see it works row-wise based on first cell entry but I would like it to test every cell matches.
    Attached Files Attached Files

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to compare cells and format conditionally over a whole sheet

    TRY

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    OK, so that returns TRUE if both cells in Sheet2, Col A and Sheet 2, Col B match their row equivalent on Sheet 1
    so now the whole row on Sheet 2 goes green if both cells in Col A and Col B match.
    ... can we tweek this so that each cell in Sheet 2 goes green if it matches the cell of the same position on Sheet1 . ie test if Sheet1 A3 = Sheet2 A3, Sheet1A4 = Sheet2 A4, ...Sheet1 B5 = Sheet2 B5 etc ??

    thanks again :-)

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to compare cells and format conditionally over a whole sheet

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ??

  10. #10
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    Ah, perfect! Just the job :-)
    Many thanks Fotis

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to compare cells and format conditionally over a whole sheet

    You are welcome and thanks for the feedback.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  12. #12
    Registered User
    Join Date
    04-30-2013
    Location
    Horsham, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: How to compare cells and format conditionally over a whole sheet

    I already did the *star* and now I have also marked the thread as solved.
    You saved me writing a macro so I'm very content with the outcome.

+ 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. Need help looping through multiple sheets to conditionally format the same range/sheet
    By xcelnovice101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2014, 10:54 AM
  2. Macro to Conditionally Format Cells based on another cells value
    By Barking_Mad in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 06-07-2013, 03:05 AM
  3. Replies: 6
    Last Post: 10-13-2011, 11:30 AM
  4. Replies: 2
    Last Post: 04-06-2006, 11:10 AM
  5. conditionally format cells
    By elad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2005, 01:06 PM

Tags for this Thread

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