Freezing Conditional Formatting

Hello,
I am using MS-Office-2000.
I have applied formula-based conditional formatting on a range of cells.
Now, I want to remove the Conditions but wanna keep the formatting on the
data as it is. This will help me in Moving the data around the sheet without
loosing the formatting.
Is there any way of achieving this ?

Thanks,
Ashish

Ashish,

I can't think of a direct way to apply a particular format of conditional
formatting to the cells as regular (permanent) formatting. can you use the
same conditional formatting at the destination? Or, instead of using
conditional formatting, you could run a macro that applied normal
formatting. Then it would copy and paste.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Ashish Chamaria" wrote in message
...
Hello,
I am using MS-Office-2000.
I have applied formula-based conditional formatting on a range of cells.
Now, I want to remove the Conditions but wanna keep the formatting on the
data as it is. This will help me in Moving the data around the sheet
without
loosing the formatting.
Is there any way of achieving this ?

Thanks,
Ashish

Actually I had to apply some colour-codes on the data based on certain
conditions. The datasheet that I have contains around 6000 cells of data. So
I created a conditional formatting formula which I copied on all the 6000
cells to get the desired color formatting for different kinds of data. The
conditional formatting formula itself is dynamic as it is not based on any
one particular cell and thats why I cant use absolute referencing in this
formula as it later has also to be applied on all the 6000 cells.

But once the cells have been colored on the basis of conditional formatting,
I want to keep the colours and remove the conditional formatting from the
cells, because now if I MOVE just a part of that data to someplace else in
the sheet, it rechecks for those conditional formatting checks as per the
new location of the data and reformats the data.

Please help.

Thanks
Ashish

"Earl Kiosterud" wrote in message
...
Ashish,

I can't think of a direct way to apply a particular format of conditional
formatting to the cells as regular (permanent) formatting. can you use
the
same conditional formatting at the destination? Or, instead of using
conditional formatting, you could run a macro that applied normal
formatting. Then it would copy and paste.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Ashish Chamaria" wrote in message
...
Hello,
I am using MS-Office-2000.
I have applied formula-based conditional formatting on a range of cells.
Now, I want to remove the Conditions but wanna keep the formatting on
the
data as it is. This will help me in Moving the data around the sheet
without
loosing the formatting.
Is there any way of achieving this ?

Thanks,
Ashish

Hi Dave,
Thanks a lot for your reply.
Its just that I wanted to share a workaround which I discovered for
achieving the task.

Just copy the data (that has been conditionally formatted) and paste it in
MS-Word document. Now, recopy the same data from MS-Word and paste it in
MS-Excel.

The formatting stays but the conditions are gone !!!

Thanks
Ashish

"Dave Peterson" wrote in message
...
This doesn't look trivial to me.

But Chip Pearson did most of the work.

I went to his site:
http://www.cpearson.com/excel/CFColors.htm

And stole his ActiveCondition function.

(I did make a change to it because of an oddity in excel: See John
Walkenbach's
site:
http://j-walk.com/ss/excel/odd/odd07.htm to see more information.)

I got help from both John and Bernie Deitrick on how to overcome this
bleeping
oddity!

(Both John's and Bernie's tip seemed to work ok for me. I included (but
commented out) John's version. I used Bernie's (simply because it was
more
simple!). (I think I would have had to activate a different worksheet in
either
case. And if I have to activate a worksheet, I might as well just select
the
cell!--it goes against a lot of things I've learned here, but you gotta do
what
works.)

Chip's code is the workhorse. It determines which condition is active.
The
code that calls it just removes the non-active format conditions and
replaces
the activecondition with True. (so it always stays active).

So no matter what's in the cell, the conditional formatting that was there
will
always apply (well, until you change it.)

I've included Chip's code here only because of the slight changes I made.

(The notes and most of the procedure came from a similar request:
http://groups.google.com/groups?thre...01E0%40msn.com)

And had this followup:

One thing that I didn't think of (and I hope that it doesn't affect you).

If you have cells that evaluate to errors (1/0, ref, n/a type stuff), then
the
ActiveCondition function blows up. The code uses a lot of .values.


===============
Try this against a copy of your worksheet--just in case.

Option Explicit
Sub testme()

Dim myRng As Range
Dim mycell As Range
Dim myCell_AC As Long
Dim wks As Worksheet
Dim i As Integer
Dim startCell As Range

Set startCell = ActiveCell

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
application.screenupdating = false
For Each mycell In myRng.Cells
If mycell.FormatConditions.Count = 0 Then
MsgBox "something bad happened with " & _
mycell.Address(external:=True)
'do nothing
Else
myCell_AC = ActiveCondition(mycell)
If myCell_AC = 0 Then
mycell.FormatConditions.Delete
Else
For i = mycell.FormatConditions.Count To 1
Step -1
If i = myCell_AC Then
mycell.Interior.ColorIndex _
= mycell.FormatConditions(i) _
.Interior.ColorIndex
End If
mycell.FormatConditions(i).Delete
Next i
End If
End If
Next mycell
application.screenupdating = true
End If
End With
Next wks

Application.Goto startCell

End Sub

Function ActiveCondition(Rng As Range) As Integer

Dim Ndx As Long
Dim FC As FormatCondition
Dim tmpRng As Range

Set tmpRng = Rng
Set Rng = Nothing
Set Rng = tmpRng

If Rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To Rng.FormatConditions.Count
Set FC = Nothing
Set FC = Rng.FormatConditions(Ndx)
Set FC = Rng.FormatConditions(Ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
If CDbl(Rng.Value) = CDbl(FC.Formula1) And _
CDbl(Rng.Value) = CDbl(FC.Formula2) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlGreater
If CDbl(Rng.Value) CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlEqual
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlGreaterEqual
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlLess
If CDbl(Rng.Value) CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlLessEqual
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlNotEqual
If CDbl(Rng.Value) CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case xlNotBetween
If CDbl(Rng.Value) = CDbl(FC.Formula1) Or _
CDbl(Rng.Value) = CDbl(FC.Formula2) Then
ActiveCondition = Ndx
Exit Function
End If

Case Else
Debug.Print "UNKNOWN OPERATOR"
End Select


Case xlExpression
' John Walkenbach 's excel oddity page
' http://j-walk.com/ss/excel/odd/odd07.htm
' describes the problem
'
' Bernie Deitrick's tip about selecting the cell first to make
formula1
' work correctly works fine, too.
'
' from John's site:
' Dim F1 As String
' Dim F2 As String'
' Rng.Parent.Activate 'make F2 formula work with activecell.
' F1 = Rng.FormatConditions(1).Formula1
' F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell)
' F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Rng)

' From Bernie's tip
Application.Goto Rng

If Application.Evaluate(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If

Case Else
Debug.Print "UNKNOWN TYPE"
End Select

Next Ndx

End If

ActiveCondition = 0

End Function



Ashish Chamaria wrote:

Actually I had to apply some colour-codes on the data based on certain
conditions. The datasheet that I have contains around 6000 cells of
data. So
I created a conditional formatting formula which I copied on all the
6000
cells to get the desired color formatting for different kinds of data.
The
conditional formatting formula itself is dynamic as it is not based on
any
one particular cell and thats why I cant use absolute referencing in
this
formula as it later has also to be applied on all the 6000 cells.

But once the cells have been colored on the basis of conditional
formatting,
I want to keep the colours and remove the conditional formatting from
the
cells, because now if I MOVE just a part of that data to someplace else
in
the sheet, it rechecks for those conditional formatting checks as per
the
new location of the data and reformats the data.

Please help.

Thanks
Ashish

"Ron_D" wrote in message
...
Ashish,
Are you totally opposed to keeping the conditional formatting? You
can
make all the cell references in the conditional formatting window
absolute
by typing "$" to the row and column address. This will ensure the
formatting stays the same when you move the cells even with the copy
method.

Otherwise, I don't think there are ways to keep the formatting when
you
eliminate the conditions.

Ron_D

--

Dave Peterson