Updating conditional format number 3

[Guest]

The cell I'm inserting conditional format number 3 in allready contains conditional formats 1 and 2. Sometimes there is also a number 3 as well. My code should replace number 3 if it exists if not just insert. This is my code:

'Adding Conditional formatting
For Each Cell In Range("Tox_Mean_I_AC")
If Not Cell.FormatConditions(3) Is Nothing Then Cell.FormatConditions(3).Delete

Low = Sheets("Parameter Settings").Range("F85") - Sheets("Parameter Settings").Range("H85")
High = Sheets("Parameter Settings").Range("F85") + Sheets("Parameter Settings").Range("H85")

Cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
Formula1:=Low, Formula2:=High
Cell.FormatConditions(3).Interior.ColorIndex = 3
Next

Unfortunately I get a "Subscribt out of range" error in line 3 when I run the code.

Can anybody help me with the correct syntax?

 

[Bernie Deitrick]

Dr.,

Change

If Not Cell.FormatConditions(3) Is Nothing Then
Cell.FormatConditions(3).Delete

to

If Cell.FormatConditions.Count = 3 Then
Cell.FormatConditions(3).Delete

Also, your code will error out if there aren't at least two conditions for
every cell prior to running. You could change
Cell.FormatConditions(3).Interior.ColorIndex = 3
to

Cell.FormatConditions(Cell.FormatConditions.Count).Interior.ColorIndex = 3

HTH,
Bernie
MS Excel MVP

The cell I'm inserting conditional format number 3 in allready contains

conditional formats 1 and 2. Sometimes there is also a number 3 as well. My
code should replace number 3 if it exists if not just insert. This is my
code:

'Adding Conditional formatting
For Each Cell In Range("Tox_Mean_I_AC")
If Not Cell.FormatConditions(3) Is Nothing Then Cell.FormatConditions(3).Delete

Low = Sheets("Parameter Settings").Range("F85") -

Sheets("Parameter Settings").Range("H85")

High = Sheets("Parameter Settings").Range("F85") +

Sheets("Parameter Settings").Range("H85")

 

[Guest]

Thanks Bernie, just what I needed.