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!
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