Here is my problem. I need to some how add several more conditional formatting options. I have tried to add all different kinds of VB to solve my problem but none of them seem to work. The one below is my favorite – but doesn’t work with my formulas. I have formulas returning different values. I need this program below (our one like it) to be “formula friendly”. When the formula result changes - I need the colors to change as well. This program doesn’t work with “if” formulas and will not reset itself when returning another cell’s values – it will work on the first returned value – but then after – “freezes up”.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
Count = 0
Do Until Count = 12
If Target.Value = Range("A1").Offset(Count, 0).Value Then
x = Range("A1").Offset(Count, 0).Interior.ColorIndex
End If
Count = Count + 1
Loop
Target.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=Target
Target.FormatConditions(1).Interior.ColorIndex = x
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Variant
Count = 0
Do Until Count = 12
If Target.Value = Range("A1").Offset(Count, 0).Value Then
x = Range("A1").Offset(Count, 0).Interior.ColorIndex
End If
Count = Count + 1
Loop
Target.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=Target
Target.FormatConditions(1).Interior.ColorIndex = x
End Sub