34

I have set up conditional formatting in a set of cells of "Sheet2" on my workbook. I'd like to reuse this formatting on "Sheet1" (I've spent quite some time setting it up). Is there a way to do it?

I know that you can copy conditional formatting in a single sheet by selecting the new cells, but I don't recall it is possible to select cells across multiple sheets.

Thanks!

25
  1. Select conditional-formatted cell/range in Sheet2
  2. CtrlC (Copy)
  3. Select target cell/range in Sheet1
  4. CtrlAltV (Paste Special) > T (Format) > OK

Alternatively,

  1. Select conditional-formatted cell/range in Sheet2
  2. Click Copy Format button in tool bar
  3. Click "Sheet1"
  4. Select target cell/range
1
  • 2
    This also copy over the background, font, etc.. It doesn't only copy over the conditional formatting. – DavidA Apr 11 '16 at 10:30
14

Alternate Answer where you don't want other formatting disrupted on the destination sheet

  1. Copy a cell from the original sheet to an UNused position in the destination sheet (not one with data in it).
  2. Open the Manage Rules option of Conditional Formatting
  3. Select Show formatting rules for: This Worksheet
  4. For each Rule, adjust the Applies to match the range you require. Easy to do, just:
    • Click the range button to the right of the Applies to
    • Click-drag-select from the top left cell to the bottom right cell
    • Click the range button to return to the Conditional Rules Manager
  5. Click OK or Apply to see the result

Try it, its way easier than it sounds!

1
  • Yes, this ^^. Doesn't interfere with other formatting and very simple. – bennji_of_the_overflow Apr 12 at 7:07
5

You can also select the cell with conditional formatting in "Sheet2"

Then click on "Format Painter" in your tool bar

Then select all the cells in "Sheet1" you like to have the same formatting.

The conditional formatting will be copied over

1
  • 2
    Nice and fast!! – StBlade May 27 '15 at 12:12
0

You can create a macro for the conditional formatting that you are creating and then execute this macro in each worksheet or workbook that you want.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.