How to copy conditional formating to another Excel Worksheet

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!

Solution 1:

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

Solution 2:

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!

Solution 3:

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