conditional formating "extension problems" HELP!!

[brink]

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

 

[Andrew Poulsom]

The Worksheet_Change event fires when data is entered by the user, not by recalculation of formulas.

What range of cells do you wish to format?

 

[brink]

I want the range to be from Z5 to IJ80. Can you help me?!?!

 

[Andrew Poulsom]

That's quit a big range - better make sure calculation is set to manual!

Private Sub Worksheet_Calculate()
    Dim c As Range
    Dim Count As Integer
    Dim x As Variant
    On Error GoTo NoFormulas
    For Each c In Range("Z5:IJ80").SpecialCells(xlCellTypeFormulas)
        Count = 0
        x = ""
        Do Until Count = 12
            If c.Value = Range("A1").Offset(Count, 0).Value Then
                x = Range("A1").Offset(Count, 0).Interior.ColorIndex
                Exit Do
            End If
            Count = Count + 1
        Loop
        If x <> "" Then
            c.FormatConditions.Delete
            c.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
            Formula1:=c
            c.FormatConditions(1).Interior.ColorIndex = x
        End If
    Next c
NoFormulas:
End Sub

 

[brink]

You are the man!! Thank you so much!!

 

[nehpets12]

As it is such a Large Range is it possible to select cells where the value is greater than 0 rather than using
For Each c In Range("F3:IB100").SpecialCells(xlCellTypeFormulas)

would this way calculate any faster