Modify a conditional format formula in code?

[Shaun]

I have a column of figures (column r starting at row 4)
with this conditional formatting formula : =$N$4+
(14*12*30.59) which changes the cell's colour to yellow.
There are two other conditional formats also, but this is
the first. The $4 in the formula needs to be changed to
the current row number.

Currently I just change it to, say, 5 (on row 5) and use
the format painter to copy this down the rest of the
cells. As there's a LOT of spreadsheets that may have
this change needed (4000-ish), is there a way to do this
in code, so that when a sheet is opened, I can run the
code and it will do it?

 

[Dave Peterson]

If you select all the cells in the range first and write your formula for the
activecell, then excel will adjust the formula for the other rows.

But if you use that absolute reference ($N$4), then that won't be adjusted.

Try changing it to $N4 and when the other cells get their version of the formula
the row number will adjust nicely.

Option Explicit
Sub testme03()

Dim myRng As Range

With ActiveSheet
Set myRng = .Range("A4:A28")
With myRng
Application.Goto myRng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$N4+(14*12*30.59)"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
End With
End With
End With
End Sub

And excel is pretty weird when it comes to using a relative reference in the
formatconditions line.

I chose to select the range first--but John Walkenbach gives a different
solution at:
http://j-walk.com/ss/excel/odd/odd07.htm

 

[Shaun]

The absolute reference is the one that wants to be
changed, so this is great thanks. Can I modify the
existing conditional formatting without deleting it?
There's 3 conditions and it's only the first of the three
that wants to be changed, or should I just delete all
three then recreate them?

 

[Dave Peterson]

I'd just delete them all and reapply.

But that's just a personal preference. (It would mean I could use the macro
recorder output pretty easily.)

 

[Shaun Allan]

Um... Is it possible to just change that one formula in
the first condition, rather than deleting and recreating?
This needs to be automatic cos other people, who might
forget to change the formula manually are going to be
accessing the sheets.

So I need to select the column of figues, then change the
formula in the first of the three conditions.

?

 

[Dave Peterson]

Sorry, I didn't notice that it was the first conditional formatting formula that
you want to modify.

You can do it, but you'll have to make sure that there is no third formula.
then move 2 to 3 (and all the formats!)

Then move 1 to 2 (and all the formats here, too)

Then insert a new #1.

If you record a macro when you set the formatting in that CF, you'll see all the
things you have to remember in code. It'll be a big, big, big pain.

But if you wanted to add a new CF formula, you could do something like:

You can find out how many formulas are used with something like:

Option Explicit
Sub testme03()

Dim iCtr As Long
Dim LastFC As Long
Dim testStr As String

With ActiveSheet
With .Range("A4")
iCtr = 1
testStr = ""
On Error Resume Next
Do
testStr = .FormatConditions(iCtr).Formula1
If Err.Number <> 0 Then
'the previous CF formula was the last
LastFC = iCtr - 1
Exit Do
End If
iCtr = iCtr + 1
Loop
On Error GoTo 0

If LastFC < 3 Then
.FormatConditions.Add Type:=xlExpression, _
Formula1:="what you want"
.FormatConditions(LastFC + 1).Interior.ColorIndex = 27
End If

End With
End With

End Sub

But just adding something as the 2nd or 3rd formula isn't equivalent to making
it the first formula.

If I had a choice, I think I'd reapply the CF (after deleting the old). And if
you're setting up the Formatting, it should(???) be easier(???).