excelvba

How do I copy/paste Interior Colors only in Excel?


I have the below code running well. I now need to copy the interior color only from the defined range (G4:G11) to another range (F4:F11) and I can't seem to figure it out.

Sub Conditional_Formatting()

Dim Scorecard As String
Scorecard = ActiveSheet.Name

Dim formatted_range As Range
Set formatted_range = Sheets(Scorecard).Range("G4:G11")

With formatted_range.FormatConditions
    .Delete
    .Add(Type:=xlExpression, Formula1:="=" & .Parent.Cells(1).Address(0, 0) & "=""""").Interior.ColorIndex = xlNone
    .Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="-.02").Interior.Color = RGB(255, 0, 0)
    .Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="-.02", Formula2:="-.0199999999").Interior.Color = RGB(255, 255, 0)
    .Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="0").Interior.Color = RGB(0, 128, 0)
End With

End Sub

Thanks in advance!


Solution

  • You need to look at the DisplayFormat.Interior.Color to pick up the conditional formatting.

    Add this code after your End With line (I tried doing it to the whole range at once, but turned the cells black):

        Dim Cell As Range
        For Each Cell In formatted_range.Cells
            Cell.Offset(, -1).Interior.Color = Cell.DisplayFormat.Interior.Color
        Next Cell
    

    Edit after answer accepted:

    If you want column G to use the conditional formatting rather than have a static colour you could use:

    Sub Conditional_Formatting()
    
        Dim Scorecard As String
        Scorecard = ActiveSheet.Name
        
        Dim formatted_range As Range
        Set formatted_range = Sheets(Scorecard).Range("F4:G11")
        
        With formatted_range
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1, 2).Address(0, 1) & "="""""
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = xlNone
            
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1, 2).Address(0, 1) & "<-0.02"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
            
            .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(" & .Cells(1, 2).Address(0, 1) & ">=-0.02," & .Cells(1, 2).Address(0, 1) & "<=-0.0199999999)"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = RGB(255, 255, 0)
            
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(1, 2).Address(0, 1) & ">0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = RGB(0, 128, 0)
        End With
    
    End Sub
    

    or to shorten it a bit use a separate procedure for the conditional formatting:

    Sub Conditional_Formatting()
    
        Dim Scorecard As String
        Scorecard = ActiveSheet.Name
        
        Dim formatted_range As Range
        Set formatted_range = Sheets(Scorecard).Range("F4:G11")
        
        With formatted_range
            .FormatConditions.Delete
            AddCF formatted_range, "=" & .Cells(1, 2).Address(0, 1) & "=""""", xlNone
            AddCF formatted_range, "=" & .Cells(1, 2).Address(0, 1) & "<-0.02", RGB(255, 0, 0)
            AddCF formatted_range, "=AND(" & .Cells(1, 2).Address(0, 1) & ">=-0.02," & .Cells(1, 2).Address(0, 1) & "<=-0.0199999999)", RGB(255, 255, 0)
            AddCF formatted_range, "=" & .Cells(1, 2).Address(0, 1) & ">0", RGB(0, 128, 0)
        End With
    
    End Sub
    
    Public Sub AddCF(TargetRange As Range, FormulaText As String, lColour As Long)
        With TargetRange
            .FormatConditions.Add Type:=xlExpression, Formula1:=FormulaText
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).Interior.Color = lColour
        End With
    End Sub
    

    enter image description here