If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Freezing Conditional Formatting
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 |
#3
|
|||
|
|||
Freezing Conditional Formatting
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 |
#4
|
|||
|
|||
Freezing Conditional Formatting
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 |
Thread Tools | |
Display Modes | |
|
|