No announcement yet.

Copy Conditional Formatting

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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???

  • #2
    Re: Copy Conditional Fromatting From Another Worksheet

    Copy, Paste Special Formats or use the Format Painter.


    • #3
      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).



      • #4
        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.


        • #5

          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)