Copy Excel Inventory Rows by Conditional Formatting Color with VBA
Question details
Copy inventory rows to a purchase-order worksheet when the cell in column U is displayed in a specified conditional-formatting color.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The workbook uses conditional formatting in column U to flag inventory items in yellow or light red, and matching rows need to be copied to a purchase-order sheet.
- Observed behavior
- Standard VBA Interior.Color does not return the color currently displayed by conditional formatting.
Confirm the conditional-formatting rules have calculated and identify the exact displayed RGB colors in column U. Update the source worksheet name, destination worksheet name, copied column range, and color codes before running the macro.
Use DisplayFormat to copy rows by their displayed conditional-formatting color
Use this method when conditional formatting, rather than a manually applied fill color, determines whether an inventory row should be copied.
The Interior.Color property reads a cell's underlying fill and can ignore the result shown by conditional formatting. DisplayFormat.Interior.Color reads the color currently displayed in Excel after conditional formatting is evaluated.
The example checks column U (column 21) for light yellow RGB(255,235,156) or light red RGB(255,199,206), then copies columns A:U to the next empty row on the purchase-order sheet.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste this macro into the module and replace "Inventory" and "Purchase Order" with your worksheet names: Sub CopyRowsByDisplayedColor() Dim wsSource As Worksheet, wsDest As Worksheet Dim lastRow As Long, nextRow As Long, i As Long Dim displayedColor As Long Set wsSource = ThisWorkbook.Worksheets("Inventory") Set wsDest = ThisWorkbook.Worksheets("Purchase Order") lastRow = wsSource.Cells(wsSource.Rows.Count, "U").End(xlUp).Row For i = 2 To lastRow displayedColor = wsSource.Cells(i, 21).DisplayFormat.Interior.Color If displayedColor = RGB(255, 235, 156) Or _ displayedColor = RGB(255, 199, 206) Then nextRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1 wsSource.Range("A" & i & ":U" & i).Copy _ Destination:=wsDest.Range("A" & nextRow) End If Next i End Sub
In the line wsSource.Range("A" & i & ":U" & i), change A:U if your purchase-order data uses different source columns. In wsDest.Range("A" & nextRow), change A if the destination table starts in another column.
Return to Excel, press Alt+F8, select CopyRowsByDisplayedColor, and choose Run. If rule results have changed, use Formulas > Calculate Now before running the macro.

Manage color-coded inventory and purchase-order workbooks with WPS Office
WPS Office is a free, lightweight alternative for working with inventory and purchase-order spreadsheets. Its familiar spreadsheet interface helps you review conditional formatting, filter flagged records, and continue working with Excel-compatible workbook formats after a seamless migration.

Frequently Asked Questions
Why does Interior.Color not detect my conditional-formatting color in Excel VBA?
Interior.Color returns the cell's base fill color. When a conditional-formatting rule changes the visible fill, use Cells(row, column).DisplayFormat.Interior.Color to read the displayed result.
How do I find the RGB value for the yellow or red color in my workbook?
Select a cell showing the desired conditional-formatting result, then inspect the rule in Home > Conditional Formatting > Manage Rules. Compare its fill color with the RGB values used in your macro, and update the RGB values if the rule uses custom colors.
Can the macro copy only selected columns instead of the entire inventory row?
Yes. Change the source range in the Copy line. For example, use wsSource.Range("A" & i & ":H" & i) to copy only columns A through H, or copy individual cells to specific destination columns.
Why are no rows copied even though the cells appear yellow or red?
Check that the macro is testing the correct column: column U is 21. Also verify the worksheet names, the displayed RGB values, and that the workbook has recalculated through Formulas > Calculate Now before the macro runs.
How can I prevent duplicate purchase-order rows when I run the macro again?
Add a status column or unique item identifier to the destination sheet, then have the macro check whether that identifier already exists before copying. You can also mark copied source rows with a value after the copy completes.




