I have a spreadsheet I recreate each week to track what projects I work on.
Part of it uses conditional formatting. I'd like to be able to copy the
colors from the conditional formtting in cell A1 to be used with different
formulas in other cells. Does anyone know how to copy those colors over?
Thanks in advance.

On Wed, 21 Jan 2009 03:38:01 -0800 (PST), Alan wrote:

On 20 Jan, 22:25, salgud wrote:
I have a spreadsheet I recreate each week to track what projects I work on.
Part of it uses conditional formatting. I'd like to be able to copy the
colors from the conditional formtting in cell A1 to be used with different
formulas in other cells. Does anyone know how to copy those colors over?
Thanks in advance.

I am assuming that you are copying colors from the conditional
formatting in cell A1 to be used with other conditional formatting ...
Try:

Sub CopyColors

Dim FCondition as FormatCondition
Dim i as integer

i=1
For Each FCondition in Activecell.FormatConditions
With ActiveCell.FormatConditions(i).Font
.ColorIndex=Range("A1").FormatConditions(i).Font.C olorIndex
End With
i=i+1
Next

end sub

Before you run this sub, select the cell whose conditions are to be
changed. If there are more conditions in the selected cell than in
cell A1 this simple code will fail.

Thanks for your reply. There are more conditions in the other cells than in
A1, but I can fix that. I just needed to know how to capture that first set
of conditions, since all of the conditional tests give the same color
scheme.This will do just fine.

On 21 Jan, 18:20, salgud wrote:
On Wed, 21 Jan 2009 03:38:01 -0800 (PST), Alan wrote:
On 20 Jan, 22:25, salgud wrote:
I have a spreadsheet I recreate each week to track what projects I work on.
Part of it uses conditional formatting. I'd like to be able to copy the
colors from the conditional formtting in cell A1 to be used with different
formulas in other cells. Does anyone know how to copy those colors over?
Thanks in advance.

I am assuming that you are copying colors from the conditional
formatting in cell A1 to be used with other conditional formatting ...
Try:

Sub CopyColors

* Dim FCondition as FormatCondition
* Dim i as integer

* i=1
* For Each FCondition in Activecell.FormatConditions
* * With ActiveCell.FormatConditions(i).Font
* * * *.ColorIndex=Range("A1").FormatConditions(i).Font. ColorIndex
* * End With
* * i=i+1
* Next

end sub

Before you run this sub, select the cell whose conditions are to be
changed. If there are more conditions in the selected cell than in
cell A1 this simple code will fail.

Thanks for your reply. There are more conditions in the other cells than in
A1, but I can fix that. I just needed to know how to capture that first set
of conditions, since all of the conditional tests give the same color
scheme.This will do just fine

Just in case, you appear to need only change one character ...

Sub CopyColors


Dim FCondition as FormatCondition
Dim i as integer


i=1
For Each FCondition in Activecell.FormatConditions
With ActiveCell.FormatConditions(i).Font
.ColorIndex=Range("A1").FormatConditions(1).Font.C olorIndex
' above FormatConditions(i) has become FormatConditions(1)
' now all conditional formats in the selected cell will change to
match
' the first (or only) conditional color in cell A1
End With
i=i+1
Next


end sub

On Thu, 22 Jan 2009 03:27:51 -0800 (PST), Alan wrote:

On 21 Jan, 18:20, salgud wrote:
On Wed, 21 Jan 2009 03:38:01 -0800 (PST), Alan wrote:
On 20 Jan, 22:25, salgud wrote:
I have a spreadsheet I recreate each week to track what projects I work on.
Part of it uses conditional formatting. I'd like to be able to copy the
colors from the conditional formtting in cell A1 to be used with different
formulas in other cells. Does anyone know how to copy those colors over?
Thanks in advance.

I am assuming that you are copying colors from the conditional
formatting in cell A1 to be used with other conditional formatting ...
Try:

Sub CopyColors

* Dim FCondition as FormatCondition
* Dim i as integer

* i=1
* For Each FCondition in Activecell.FormatConditions
* * With ActiveCell.FormatConditions(i).Font
* * * *.ColorIndex=Range("A1").FormatConditions(i).Font. ColorIndex
* * End With
* * i=i+1
* Next

end sub

Before you run this sub, select the cell whose conditions are to be
changed. If there are more conditions in the selected cell than in
cell A1 this simple code will fail.

Thanks for your reply. There are more conditions in the other cells than in
A1, but I can fix that. I just needed to know how to capture that first set
of conditions, since all of the conditional tests give the same color
scheme.This will do just fine

Just in case, you appear to need only change one character ...

Sub CopyColors


Dim FCondition as FormatCondition
Dim i as integer


i=1
For Each FCondition in Activecell.FormatConditions
With ActiveCell.FormatConditions(i).Font
.ColorIndex=Range("A1").FormatConditions(1).Font.C olorIndex
' above FormatConditions(i) has become FormatConditions(1)
' now all conditional formats in the selected cell will change to
match
' the first (or only) conditional color in cell A1
End With
i=i+1
Next


end sub

Thanks again, Alan. You should know, I was told less that a year ago by
some of the heavy hitters in this forum that this was not doable, or just
too difficult to bother with! Seriously. I decided to try again because
it's the only thing, so far, that I couldn't find a way to do with VBA.

Happy to help.