+ Reply to Thread
Results 1 to 8 of 8

Copy a conditional format for a row across an entire sheet

  1. #1
    Registered User
    Join Date
    10-25-2010
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    13

    Copy a conditional format for a row across an entire sheet

    Hi All,
    I have attached a snapshot of data.
    This workbook has 1000 rows, and we need to identify duplicate prices for each row (each row is a product, the columns from G onward are supplier quotes).
    How do I quickly replicate this conditional format across the entire workbook?
    Thanks very much for any assistance.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Copy a conditional format for a row across an entire sheet

    1st, delete ALL the CF from ALL rows under row 2
    2nd, go into the CF rul in row 2 and change the APPLIES TO, to cover the entire range you want


    A word of caution - applying this to entire columns or "format across the entire workbook?" may slow your file down. Try and restrict CF to only what you need
    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
    Registered User
    Join Date
    10-25-2010
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy a conditional format for a row across an entire sheet

    Thanks for your reply.
    Unfortunately if I apply the CF formula to the entire sheet, it will pickup dupe values across the entire workbook, not just for each row. Essentially I'm wondering if there's a shortcut instead of highlighting each row, clicking CF, clicking duplicate values, and then moving to the next row.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Copy a conditional format for a row across an entire sheet

    oops missed that.

    Try it this way instead.

    Instead of using the Duplicates rule, use the Use Formula rule, with this formula in it...
    =COUNTIF($G2:$N2,G2)>1

    Again, highlight the entire range you need this applied to

  5. #5
    Registered User
    Join Date
    10-25-2010
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy a conditional format for a row across an entire sheet

    Thanks again.
    Unfortunately once entering that formula it is now highlighting multiple values across each row, not just duplicate values. How does the formula know to highlight dupe values?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Copy a conditional format for a row across an entire sheet

    The =COUNTIF($G2:$N2,G2)>1 does a count for that row, and highlights anything that has a count higher than 1.

    See the attached
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-25-2010
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Copy a conditional format for a row across an entire sheet

    Absolutely fantastic. Thank you FDibbins! works a treat.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,217

    Re: Copy a conditional format for a row across an entire sheet

    Happy to help

    If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

+ 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 Format entire row
    By Dean81 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2012, 06:19 AM
  2. Copy Entire row to another sheet if it has conditional formatting
    By imogul in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2011, 01:22 PM
  3. Conditional format entire row?
    By Pyrex238 in forum Excel General
    Replies: 8
    Last Post: 06-03-2011, 12:09 PM
  4. automatically copy a cell (due to a conditional format) to another sheet
    By marc eber in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2008, 12:19 PM
  5. conditional format of entire row
    By erich_7719 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-12-2008, 07:30 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