The Run-time error '438' you’re encountering happens because you’re trying to access the Type property directly on the FormatConditions collection (a group of conditional formatting rules) instead of checking each individual FormatCondition object within that collection. A single cell can have multiple conditional formatting rules, so you need to loop through them to find if any match the xlUniqueValues type.

Step-by-Step Fix & Modified Code #

Here’s the adjusted code with key fixes explained, plus cleanup for reliability:

Sub LoopForCondFormatCells()
    Dim sht3 As Worksheet, sht4 As Worksheet
    Dim ColB As Range, c As Range
    Dim fc As FormatCondition ' Variable to check each conditional format rule
    Dim HosKvikOff As Range ' Output starting range (adjust to your actual cell)
    
    ' Initialize worksheet references
    Set sht3 = Sheets("Compare")
    Set sht4 = Sheets("Print ready")
    
    ' Define the target range to scan (G3:G86)
    Set ColB = sht3.Range("G3:G86")
    
    ' Set your output starting cell (replace with your actual "HosKvikOff" reference)
    Set HosKvikOff = sht4.Range("A1")
    
    For Each c In ColB.Cells
        ' Loop through every conditional format rule on the current cell
        For Each fc In c.FormatConditions
            If fc.Type = xlUniqueValues Then
                ' Copy left and right adjacent cells (F and H columns for G column cells)
                sht3.Range(c.Offset(0, -1), c.Offset(0, 1)).Copy
                
                ' Paste to the Print ready sheet, then move to next empty row
                HosKvikOff.PasteSpecial xlPasteAll
                Set HosKvikOff = HosKvikOff.Offset(1, 0)
                
                Exit For ' Stop checking other rules once we find the matching one
            End If
        Next fc
    Next c
    
    ' Clear copy/paste mode to avoid lingering selection
    Application.CutCopyMode = False
End Sub

Key Fixes Explained #

• Fixed the conditional format check: Instead of c.FormatConditions.Type, we loop through each FormatCondition in the cell's FormatConditions collection. This is the core fix for the 438 error, since you can’t access properties directly on the collection itself.
• Simplified range handling: Removed error-prone address string conversions—we use Offset directly on the cell object to target adjacent cells.
• Corrected variable declarations: Your original code had Dim sht3, sht4 As Worksheet which only declared sht4 as a Worksheet (sht3 was a Variant). We explicitly declare both as Worksheet for clarity and safety.
• Initialized the output range: Your original code used HosKvikOff without defining it, which would cause another error. We added a placeholder for you to adjust to your actual starting cell in the Print ready sheet.
• Added Exit For: Once we find the xlUniqueValues rule for a cell, we don’t waste time checking other rules for that same cell.

Optional Adjustment #

If you only want to copy the left and right cells (excluding the original G column cell), replace the copy line with:

sht3.Union(c.Offset(0, -1), c.Offset(0, 1)).Copy

内容的提问来源于stack exchange，提问作者Patrick S