Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 5 of 5

Thread: Copy Conditional Formatting

  1. #1
    Join Date
    26th June 2008

    Copy Conditional Formatting

    I currently have a week summary sheet (that has conditional formatting) and also several other sheets with the same data broken up into days. Obviously the days copy the data directly from the summary sheet, however is there a way to copy the conditional formatting. I currently have a function that can return the number of the conditional format that a particular cell passed, however can not pass a different worksheet through the conditional formatting. I've tried the indirect function but to no end.

    Summary sheet:
    Object Mon Tues
    A 2 10
    B 7 8
    C 3 5

    Cell A2 is blue/bold due to conditional format rule "2".

    Monday Sheet:
    Object Number
    ='Summary'!A2 ='Summary'!B2

    I've tried conditional format for B2 as (using my CondNumber function):
    =CondNumber('Summary'!B2)=2 .... then format to blue

    This is where i get the 'references to other worksheets' error ... any ideas???

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    24th January 2003

    Re: Copy Conditional Fromatting From Another Worksheet

    Copy, Paste Special Formats or use the Format Painter.

  3. #3
    Join Date
    26th June 2008

    Re: Copy Conditional Formatting


    The only problem with that is that the original conditional formatting in the Summary sheet is quite complex and refers to other parts of the summary sheet (that is not available in the daily sheets).


    Excel Video Tutorials / Excel Dashboards Reports

  4. #4
    Join Date
    23rd April 2008
    Work in North Wales

    Re: Copy Conditional Formatting

    Kenny, can't really comment specifically without seeing your workbook, but I would add to Dave's tip list:

    If you select a cell with conditional formatting, then multi-select another cell (i.e. Ctrl+click) which doesn't have conditional formatting, then go Format>Conditional Formatting, Excel will apply the formatting from the cell which has it to the cell which doesn't have it.

    Much easier than it sounds when I explain it...

    EDIT: The other thing which springs to mind is the use of named ranges in conditional formatting - as you say, you can't refer to other sheets but you CAN refer to named ranges.

    Excel Video Tutorials / Excel Dashboards Reports

  5. #5
    Join Date
    26th June 2008

    Re: Copy Conditional Formatting

    Mmm, my deepest apologies to Dave.
    I did try this and it seems to work well - not sure why i thought it wasn't going to work.
    Can't believe I just spent 2 days on this scouring the net for answers.

    Regardless, thanks for your direction.


    PS: named ranges would've been cumbersome as I would have had to create about 300 named ranges (one for each cell)

    Excel Video Tutorials / Excel Dashboards Reports

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Copy Conditional Formatting Between Ranges
    By riteoh in forum EXCEL HELP
    Replies: 2
    Last Post: March 7th, 2008, 01:11
  2. Copy values from cells with conditional formatting
    By nettedavid in forum EXCEL HELP
    Replies: 7
    Last Post: January 10th, 2005, 23:10
  3. copy conditional formatting
    Replies: 3
    Last Post: December 14th, 2004, 15:21
  4. Conditional Formatting: copy conditional formats
    By excelWalter in forum EXCEL HELP
    Replies: 2
    Last Post: November 2nd, 2003, 11:11
  5. Replies: 2
    Last Post: August 20th, 2003, 10:03


Posting Permissions

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