Skip White color from copying conditional formatting color from one range to another VBA Excel

0
votes

I have a huge data formatted with conditional formatting but only 10% of the cells are colored by conditional formatting so i want to copy only colored cells color to a copied range

this a code to copy conditional formatting color from range to range. I want to know how to skip white color from copying so that i can reduce this loop time by 90%

Sub MatchColors2()
Dim rngTo As Excel.Range
Dim rngFrom As Excel.Range
Dim i As Long
Dim j As Long

Set rngFrom = ActiveSheet.Range("C5:G1000")
Set rngTo = ActiveSheet.Range("I5:M1000")

For i = 1 To rngFrom.Areas.Count
    For j = 1 To rngFrom.Areas(i).Cells.Count
        rngTo.Areas(i).Cells(j).Interior.Color =rngFrom.Areas(i).Cells(j).DisplayFormat.Interior.Color
    Next j
Next i
End Sub

Thanks

1
excelloopsformattingconditionalvba

1 Answers

1
votes

If you mean that you only want to copy colors of cells where a Conditional Format has been activated, add a test like this

(BTW since your rng's have only one Area you don't need the outer loop)

For j = 1 To rngFrom.Cells.Count
    If rngFrom.Cells(j).DisplayFormat.Interior.Color <> rngFrom.Cells(j).Interior.Color Then
        rngTo.Cells(j).Interior.Color = rngFrom.Cells(j).DisplayFormat.Interior.Color
    End If
Next j