copy conditional formats

I thought it might help if I showed you the abridged code:

Option Explicit
Dim R1 As Range
Dim R2 As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Sel As Range
Dim myRet As Variant
Dim bCheck As Boolean
Sub CopyCFFormatsA()
Set Sel = Selection
Set R1 = Range("c1:c10")
Set R2 = Range("d1:d10")
j = 1
Application.EnableEvents = False
For i = 1 To R1.Rows.Count
R1.Cells(i, j).Select
myRet = CheckFormat(R1.Cells(i, j))
If myRet = False Then GoTo NoCF
If myRet = "None" Then GoTo NoCF
R2.Cells(i, j).Interior.colorindex = _
R1.Cells(i, j).FormatConditions(myRet).Interior.colorindex
NoCF:
Next i
Sel.Select
Application.EnableEvents = True
End Sub
Function CheckFormat(c As Range) As Variant
CheckFormat = "None"
For k = 1 To c.FormatConditions.Count
bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
If bCheck Then
CheckFormat = k
bCheck = False
Exit Function
End If
Next k
CheckFormat = "None"
End Function--
Bill Roberts


"Bill Roberts" wrote:

> Bernie, I still cannot get the macro to work properly. There are 2 rows in
> column C. Both cells are conditionally formatted based on the equation
> “A1-B1”. If A>B, then fill the cell yellow. If A1<B1, do not fill any
> color. In this case, C1 is yellow (meets the format condition), and C2 is
> blank (does not meet the format condition). When I step through the code you
> suggested, the command line
> bCheck = Application.Evaluate(c.FormatConditions.Item(i).Formula1)
> formats both C1 and C2 yellow. It is as if the command line only checks to
> see if there is ANY conditional formatting (Type 2 condition), and if there
> is, it fills the cell. I am trying to duplicate the fill color (including no
> fill), but eliminate the conditional format equations. I have tried running
> macros that just get the colorindex, but they don’t work with conditionally
> formatted cells. Would appreciate any ideas.
>
> --
> Bill Roberts
>
>
> "Bill Roberts" wrote:
>
> > I have a column that has been conditionally formatted with equations, using
> > the "fill" command to color the cells based on the relative values of cells
> > in other columns. I want to copy only the colors to another column, but not
> > the equations. As if I would do a copy/paste special/values, but the
> > conditionally formatting equations refuse to go away. I just want only the
> > color patterns in the new column. Would appreciate any suggestions. TIA
> > Bill Roberts
 >> Stay informed about: copy conditional formats 

Bill,

Did you leave in the line?

R1.Cells(i, j).Select

Excel evaluated CF formulas as if it were copied into the currently selected
cell - so if the selection doesn't change, the formula evaluates to only one
value.

Bernie


"Bill Roberts" wrote in message

> Bernie, I still cannot get the macro to work properly. There are 2 rows
> in
> column C. Both cells are conditionally formatted based on the equation
> “A1-B1”. If A>B, then fill the cell yellow. If A1<B1, do not fill any
> color. In this case, C1 is yellow (meets the format condition), and C2 is
> blank (does not meet the format condition). When I step through the code
> you
> suggested, the command line
> bCheck = Application.Evaluate(c.FormatConditions.Item(i).Formula1)
> formats both C1 and C2 yellow. It is as if the command line only checks
> to
> see if there is ANY conditional formatting (Type 2 condition), and if
> there
> is, it fills the cell. I am trying to duplicate the fill color (including
> no
> fill), but eliminate the conditional format equations. I have tried
> running
> macros that just get the colorindex, but they don’t work with
> conditionally
> formatted cells. Would appreciate any ideas.
>
> --
> Bill Roberts
>
>
> "Bill Roberts" wrote:
>
>> I have a column that has been conditionally formatted with equations,
>> using
>> the "fill" command to color the cells based on the relative values of
>> cells
>> in other columns. I want to copy only the colors to another column, but
>> not
>> the equations. As if I would do a copy/paste special/values, but the
>> conditionally formatting equations refuse to go away. I just want only
>> the
>> color patterns in the new column. Would appreciate any suggestions. TIA
>> Bill Roberts
 >> Stay informed about: copy conditional formats 

Bill,

Your version of the code worked perfectly, but I'm testing it in XL 2003. I
will have to try to locate a machine with XL2007 to try it out on, and I
will post back in the morning.

Bernie

"Bill Roberts" wrote in message

>I thought it might help if I showed you the abridged code:
>
> Option Explicit
> Dim R1 As Range
> Dim R2 As Range
> Dim i As Integer
> Dim j As Integer
> Dim k As Integer
> Dim Sel As Range
> Dim myRet As Variant
> Dim bCheck As Boolean
> Sub CopyCFFormatsA()
> Set Sel = Selection
> Set R1 = Range("c1:c10")
> Set R2 = Range("d1:d10")
> j = 1
> Application.EnableEvents = False
> For i = 1 To R1.Rows.Count
> R1.Cells(i, j).Select
> myRet = CheckFormat(R1.Cells(i, j))
> If myRet = False Then GoTo NoCF
> If myRet = "None" Then GoTo NoCF
> R2.Cells(i, j).Interior.colorindex = _
> R1.Cells(i, j).FormatConditions(myRet).Interior.colorindex
> NoCF:
> Next i
> Sel.Select
> Application.EnableEvents = True
> End Sub
> Function CheckFormat(c As Range) As Variant
> CheckFormat = "None"
> For k = 1 To c.FormatConditions.Count
> bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1)
> If bCheck Then
> CheckFormat = k
> bCheck = False
> Exit Function
> End If
> Next k
> CheckFormat = "None"
> End Function--
> Bill Roberts
>
>
> "Bill Roberts" wrote:
>
>> Bernie, I still cannot get the macro to work properly. There are 2 rows
>> in
>> column C. Both cells are conditionally formatted based on the equation
>> “A1-B1”. If A>B, then fill the cell yellow. If A1<B1, do not fill any
>> color. In this case, C1 is yellow (meets the format condition), and C2
>> is
>> blank (does not meet the format condition). When I step through the code
>> you
>> suggested, the command line
>> bCheck = Application.Evaluate(c.FormatConditions.Item(i).Formula1)
>> formats both C1 and C2 yellow. It is as if the command line only checks
>> to
>> see if there is ANY conditional formatting (Type 2 condition), and if
>> there
>> is, it fills the cell. I am trying to duplicate the fill color
>> (including no
>> fill), but eliminate the conditional format equations. I have tried
>> running
>> macros that just get the colorindex, but they don’t work with
>> conditionally
>> formatted cells. Would appreciate any ideas.
>>
>> --
>> Bill Roberts
>>
>>
>> "Bill Roberts" wrote:
>>
>> > I have a column that has been conditionally formatted with equations,
>> > using
>> > the "fill" command to color the cells based on the relative values of
>> > cells
>> > in other columns. I want to copy only the colors to another column,
>> > but not
>> > the equations. As if I would do a copy/paste special/values, but the
>> > conditionally formatting equations refuse to go away. I just want
>> > only the
>> > color patterns in the new column. Would appreciate any suggestions.
>> > TIA
>> > Bill Roberts
 >> Stay informed about: copy conditional formats