none
excel 2007 - copy conditional formatting

    Question

  • I have setup conditional formatting that changes the fill colour of row 3.

    The condional formatting uses this formula =$E$3<$K$1

    Is there a way of copying the conditional formatting to apply it to other rows? I have tried using the Format Painter tool. I have also tried removing the dollar signs from E3.

    Tuesday, October 04, 2011 11:02 AM

Answers

  • There is no problem with the copy and paste part of this, however the results are more than likely not as required. You can verify the copy has worked by using Manage rules and set the dialog to show rules for the worksheet.

    The initial formula checks to see if E3 is less than K1 and formats the row.

    Copying the formatting to another row, lets say row 9.  If this is done by selecting the row number and then using the Paste Special.  Row 9 will then use the same formula.

    If the intention is to have the value in column E compared with K1 then the intial formula should have been

    =$E3<$K$1

    that way when copied to row 9 it would adjust the row number accordingly, and the row would highlight independantly of the value in E3.

    Hope this helps


    G North MCT
    • Marked as answer by nivlektech Tuesday, October 04, 2011 3:44 PM
    Tuesday, October 04, 2011 1:47 PM

All replies

  • Hi

    Did you try copy>Paste Special and select Formats

    HTH


    Cimjet
    Tuesday, October 04, 2011 12:28 PM
  • Yes, this just pastes in the formatting of the cell, it does not copy the conditional formatting unfortunately.
    Tuesday, October 04, 2011 12:34 PM
  • I'm using XL03 and just double check and it's pasting the Conditional formatting.

     


    Cimjet
    Tuesday, October 04, 2011 12:42 PM
  • i just tried the Format Painter and it also works. Someone with XL07 needs to step in the conversation.
    Cimjet
    Tuesday, October 04, 2011 12:49 PM
  • There is no problem with the copy and paste part of this, however the results are more than likely not as required. You can verify the copy has worked by using Manage rules and set the dialog to show rules for the worksheet.

    The initial formula checks to see if E3 is less than K1 and formats the row.

    Copying the formatting to another row, lets say row 9.  If this is done by selecting the row number and then using the Paste Special.  Row 9 will then use the same formula.

    If the intention is to have the value in column E compared with K1 then the intial formula should have been

    =$E3<$K$1

    that way when copied to row 9 it would adjust the row number accordingly, and the row would highlight independantly of the value in E3.

    Hope this helps


    G North MCT
    • Marked as answer by nivlektech Tuesday, October 04, 2011 3:44 PM
    Tuesday, October 04, 2011 1:47 PM
  • Yes, that's it working now - many thanks!
    Tuesday, October 04, 2011 3:42 PM
  • Glad to be of assistance
    G North MCT
    Tuesday, October 04, 2011 3:45 PM