Changing a conditional formatting formula?

[Shaun Allan]

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, is there a way to do this in code?

 

[Bob Phillips]

Select N4 down, and then change the 1st formula to

=$N4+(14*12*30.59)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shaun Allan]

Can I do this in code? I don't know how to just modify
the first of the three conditions.

 

[Bob Phillips]

Shaun,

If you select all of the cells that this condition applies top first, and
then change condition 1, the others will all adjust accordingly.

No need for code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shaun Allan]

I see what you're saying mate, but I need to do it in
code. There's around 4000 spreadsheets, visited on an
ongoing basis, that could potentially need this change, so
I need the code to modify the condition.

 

[Myrna Larson]

You'll get a lot of help by opening one of the workbooks, then turning on the
macro recorder while you perform the task manually. I just did that and here's
the code that was produced:

Sub ReFormat()
Range("A118").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1>100"
Selection.FormatConditions(1).Interior.ColorIndex = 27
End Sub

From looking at the example code for FormatConditions, the formulas all seem
to use A1 references rather than R1C1, so your code would have to active the
correct cell before modifying the formula. If the correction is to change
*all* absolute references to relative, something like this would work:

Dim C as Long
Dim F As String

With ActiveWorkbook.Worksheets(1).Range("A1:N100")
For C = 1 To .FormatConditions.Count
With .FormatConditions(C)
F = Replace(.Formula1, "$", "")
.Modify Type:=xlExpression, .Formula1:=F
End With
Next C
End With