Excel: Make conditional formatting static?

The following idea was taken from here although modified to fit some new conditional formatting structures and your needs.

Up vote 2 down vote favorite 2 share g+ share fb share tw.

I'm trying to export a range of a Excel Sheet to a new Workbook, with identical appearance but no formulas, links, etc. The problem here is that I have conditional formatting that relies on calculations outside exported range. I've tried saving the workbook to . Html, oddly enough the formatting shows in IE but not when reopening it in Excel.

Excel vba excel-vba excel-2003 link|improve this question edited Jan 14 '11 at 16:57belisarius36.2k74791 asked Jan 14 '11 at 15:51Martin778111 82% accept rate.

The following idea was taken from here, although modified to fit some new conditional formatting structures and your needs. It works like this: Given a workbook with some conditional formatting (make a copy of yours), you put in Sub a() the range of cells you want to transform from conditional to straight formatting, and run the macro. After that, just delete manually the conditional formats, and presto!

Sorry about the code length ... life is sometimes like this :( Option Explicit Sub a() Dim iconditionno As Integer Dim rng, rgeCell As Range Set rng = Range("A1:A10") For Each rgeCell In rng If rgeCell. FormatConditions. Count 0 Then iconditionno = ConditionNo(rgeCell) If iconditionno 0 Then rgeCell.Interior.

ColorIndex = rgeCell. FormatConditions(iconditionno).Interior. ColorIndex rgeCell.Font.

ColorIndex = rgeCell. FormatConditions(iconditionno).Font. ColorIndex End If End If Next rgeCell End Sub Private Function ConditionNo(ByVal rgeCell As Range) As Integer Dim iconditionscount As Integer Dim objFormatCondition As FormatCondition For iconditionscount = 1 To rgeCell.

FormatConditions. Count Set objFormatCondition = rgeCell. FormatConditions(iconditionscount) Select Case objFormatCondition.

Type Case xlCellValue Select Case objFormatCondition. Operator Case xlBetween: If Compare(rgeCell. Value, ">=", objFormatCondition.

Formula1) = True And _ Compare(rgeCell. Value, "=", objFormatCondition. Formula2) = True Then _ ConditionNo = iconditionscount Case xlGreater: If Compare(rgeCell.

Value, ">", objFormatCondition. Formula1) = True Then _ ConditionNo = iconditionscount Case xlEqual: If Compare(rgeCell. Value, "=", objFormatCondition.

Formula1) = True Then _ ConditionNo = iconditionscount Case xlGreaterEqual: If Compare(rgeCell. Value, ">=", objFormatCondition. Formula1) = True Then _ ConditionNo = iconditionscount Case xlLess: If Compare(rgeCell.

Value, "", objFormatCondition. Formula1) = True Then _ ConditionNo = iconditionscount If ConditionNo > 0 Then Exit Function End Select Case xlExpression If Application. Evaluate(objFormatCondition.

Formula1) Then ConditionNo = iconditionscount Exit Function End If End Select Next iconditionscount End Function Private Function Compare(ByVal vValue1 As Variant, _ ByVal sOperator As String, _ ByVal vValue2 As Variant) As Boolean If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application. Evaluate(vValue1) If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application. Evaluate(vValue2) If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1) If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2) Select Case sOperator Case "=": Compare = (vValue1 = vValue2) Case "": Compare = (vValue1 > vValue2) Case ">=": Compare = (vValue1 >= vValue2) Case "": Compare = (vValue1 vValue2) End Select End Function.

Thanks, I'll try that on Monday. Pretty insane that there's no . IsActive property on FormatCondition (or a direct way to get the computed format of a cell).

– Martin Jan 15 '11 at 20:18 @Martin You should check first is @Chris' answer is not enough. It's easier (but you are forced to delete all your formulas in the range) – belisarius Jan 15 '11 at 20:30 I ended up simply changing my conditionals to only link to cells inside the area I want to export, thus they should continue to work. Your answer is the generic solution to this problem, so I selected it as the correct answer.

– Martin Jan 18 '11 at 19:44.

I hate it when people say "hey, why aren't you doing that whole thing this other way", but I'll just throw it out there: when I've wanted to do this in the past, I've done it by copying the worksheet in question and pasting the formulas as values. This will freeze the conditional formatting obviously, but also means that recalculating the workbook won't leave you with values that are no longer appropriate for the formatting that's sitting on them. If this doesn't work, belisarius' code looks great.

Not bad at all if it doesn't violate other OP's needs. +1 – belisarius Jan 14 '11 at 19:10 Thx - copying values is part of the plan. One problem here is that I need only a portion of the sheet and delete the other rows/columns, but the condition links to cells outside that portion.

Another (minor) problem with copy values is that it kills any sub-cell formatting in text cells. – Martin Jan 15 '11 at 20:40.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.