Formatting Issues

How to Copy Cell Color or Strikethrough Based on a Match in Excel

Anum Mushtaq Oct 15, 2026 869 views

Question details

The user needs to automatically apply the background color or strikethrough formatting of a source cell to a destination cell when their text values match across different sheets, even in a shared workbook.

How to Copy Cell Color or Strikethrough Based on a Match in Excel

Product: Excel
Device & OS: not provided
Scenario: Synchronizing visual formats between matching data points across multiple worksheets in a shared Excel environment.
Observed behavior: Standard lookup formulas pull the data value but leave the formatting behind. The target cells remain unformatted unless manual intervention occurs or a specific rule/macro is applied.

Before you start

Determine whether the formatting in your source cells was applied manually or through a conditional formatting rule, as this dictates whether you can use a formula or must use a VBA macro.

Solution 1Recommended

Replicate Formatting Using Conditional Formatting Rules

Use this method if the original cell's color or strikethrough is driven by a conditional formatting rule. You can apply the identical logic to the target cells.

Standard Excel formulas cannot 'read' formats, but conditional formatting can apply identical formats if the same underlying condition is met. By setting up a formula-based rule on the destination sheet that checks for the matching condition on the source sheet, the visual appearance will perfectly synchronize.

Select the destination range

Highlight the cells on your target sheet where you want the matched color or strikethrough formatting to appear.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Choose formula-based formatting

Select 'Use a formula to determine which cells to format' from the list of rule types.

Enter the matching logic and apply format

Input a formula that validates the match (e.g., using COUNTIF or VLOOKUP logic that returns TRUE). Then, click the 'Format' button, set your desired background color or strikethrough effect, and click 'OK'.

Replicate Formatting Using Conditional Formatting Rules

Shared Workbooks: Conditional formatting rules are fully supported in shared workbooks and will seamlessly update for all users as data changes.

Solution 2

Copy Manual Formatting Using a VBA Macro

If the source cells were colored or struck through manually (not by a rule), standard formulas cannot detect this. A VBA macro is required to extract and copy these visual properties.

Efficient Spreadsheet Management

Seamlessly Manage Cell Formatting with WPS Spreadsheet

WPS Spreadsheet provides powerful conditional formatting tools and macro support, allowing you to synchronize data and visual formats effortlessly across multiple sheets without complex workarounds.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing data workbook.
2. Select your target cells: Highlight the destination range where the color or strikethrough formatting should be mirrored.
3. Create a formatting rule: Navigate to the Home tab, click Conditional Formatting, and select 'New Rule'.
4. Apply matching formulas: Choose the formula option, enter your cross-sheet matching logic, and configure the background color or font strikethrough.
5. Save your document: Save your progress in standard .xlsx format to ensure the formatting functions perfectly for all users.

100% compatible with Microsoft Excel formats, including .xlsx and macro-enabled .xlsm filesAdvanced conditional formatting interface to easily match styles across sheetsBuilt-in support for VBA macros to automate complex formatting tasksFree and lightweight alternative to Microsoft Office with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula only return text and not the cell color?

VLOOKUP and other standard spreadsheet formulas are designed exclusively to retrieve data values. They cannot read or extract cell metadata such as background colors, font styles, borders, or strikethrough formatting.

Will conditional formatting rules slow down my shared workbook?

Excessive use of complex formula-based conditional formatting across thousands of rows can impact performance. To optimize your shared workbook's speed, apply the rules only to your specific data ranges rather than selecting entire columns.

How do I copy formatting manually if I don't want to use formulas or VBA?

You can use the Format Painter tool. Select the source cell with the correct color or strikethrough, click the 'Format Painter' icon on the Home tab, and then click the target cell on your other sheet. Note that this is a one-time copy and will not update dynamically if the original cell changes.

Document & File Issues