Hard code conditional?

[Trikageon]

Is there a way to hardcode the results of a conditional format? For example, I have 2 columns of data that I'd like to hard code the borders produced by conditional formatting. I tried copying the columns, doing a paste special, and then selecting "formats," but that didn't work.

As an alternative, if anyone knows of an if/then formula that would return borders around a cell, that would work too, assuming copying, doing a paste special, and selecting formats would also keep those in place. Thanks in advance!

 

[BrianB]

I suggest you start by recording a macro of the formatting.

 

[Trikageon]

That's what I'm trying to do, but in order to do so I need my question answered.

 

[BrianB]

I record a conditional format and get this :-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 17/05/2010 by Brian
'
'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="0"
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

 

[Trikageon]

I'm not sure you're understanding my dilemna. I have a worksheet, and within that worksheet there are cells that are formatted a certain way because they have met certain criteria specified using conditional formatting.

Now, as-is, as soon as the data changes - the formatting will change. I don't want that. I want to, in effect, "hard code" the formatting the way it is now, so that now when the data changes, the formatting doesn't.

Hopefully that makes things more clear!

 

[BrianB]

I see now. It is necessary to use code to evaluate each cell with its conditions and see if any condition applies to apply a normal cell format.

Have a look at this

http://excel.tips.net/Pages/T001947_Removing_Conditional_Formats_but_Not_the_Effects.html

 

[Trikageon]

That definitely seems like it's going in the right direction, however, I'm getting an error on a portion of the code that reads:

If CDbl(rng.Value) = CDbl(FC.Formula1) Then

Also, it seems to be wanting me to point to a particular cell, instead of a range. For clarification, I'd like to hard-code the conditional formatting for columns G:H. Do you have any insight you could offer?

 

[dd_]

For a non-VBA solution check out the following:
http://www.mrexcel.com/forum/excel-...coding-conditional-formating.html#post3330379

Basically do the following
--make a temporary table with the conditional formula, putting out text or a number {=if(A1=0,"A",1)}

--select all text within the table (Go To, Special, Formulas--Text only)

--Excel will select all the cells that have A (that meet whatever criteria you put in the formula)
--select whatever format you want--it will be applied to each "A" cell

--voila! Hard coded formatting