Changing a conditional formatting formula?
 

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?

Changing a conditional formatting formula?
 

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" wrote in message
...
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?

Changing a conditional formatting formula?
 

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

-----Original Message-----
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" wrote
in message
...
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?


.

Changing a conditional formatting formula?
 

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" wrote in message
...
Can I do this in code? I don't know how to just modify
the first of the three conditions.

-----Original Message-----
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" wrote
in message
...
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?


.

Changing a conditional formatting formula?
 

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.


-----Original Message-----
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" wrote
in message
...
Can I do this in code? I don't know how to just modify
the first of the three conditions.

-----Original Message-----
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"
wrote
in message
...
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?


.



.

Changing a conditional formatting formula?
 

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("A1:D18").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1100"
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

On Sun, 25 Jul 2004 03:10:08 -0700, "Shaun Allan"
wrote:

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.


-----Original Message-----
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" wrote
in message
...
Can I do this in code? I don't know how to just modify
the first of the three conditions.

-----Original Message-----
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"
wrote
in message
...
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?


.



.