Solved In Excel Apply formulas from CF into cells that have data

February 12, 2013 at 12:03:59
Specs: Windows 7
In Excel how do I copy and paste formulas from Conditional Formating to a column of cells without deleting the existing data contained in those cells or having to go cell by cell? Selecting the Copy then Paste Special, Formula doesn't work. That just copies the data from the current cell into the new cell appling the formulas but replacing the data. Any help would be greatly appreciated.

See More: In Excel Apply formulas from CF into cells that have data

Report •

#1
February 12, 2013 at 12:18:29
✔ Best Answer
Have you tried Copy/Paste Special/Formats. Or you can change the "Applies to" section in the CF.

To get more specific help you'll need to explain you current CF and how you want to extend or replicate it.


Report •

#2
February 12, 2013 at 12:46:06
I'm not understanding what you are trying to do. If you try to paste a formula (or anything) into a cell, it is going to replace whatever is in the cell. How could it not?

Are you trying to replicate the Conditional Formatting currently set for one range to an additional range? If so, that has to be done through the "Rules" feature of Conditional Formatting.

If that's not it, please explain further.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 13, 2013 at 09:13:29
Thank you very much AlteK and DerbyDad03. Changing what the formula "Applies To" in the " Manage Rules" section worked perfectly. However Is there a way to make the Formulas in the Conditional Formatting Tab apply to column without specifing a data range? The three formulas that I am using now are as follows:
FORMULA: =M7=""
FORMAT: Clear
APPLIES TO: =$M$7:$M$254
FORMULA: =IF(TODAY()>+EDATE(M7,12),TRUE,FALSE)
FORMAT: Red
APPLIES TO: =$M$7:$M$254
FORMULA: =IF(TODAY()>EDATE(M7,9),TRUE,FALSE)
FORMAT: Yellow
APPLIES TO: =$M$7:$M$254

Report •

Related Solutions

#4
February 13, 2013 at 09:34:30
apply to column without specifing a data range?

Yes there is, but I would strongly advise against it,
as this can add unnecessary overhead to your spreadsheet.

For an entire column: $M:$M

MIKE

http://www.skeptic.com/


Report •

#5
February 13, 2013 at 12:35:11
You could use Macros to apply the CFs. I've derived the code mostly from the macro recorder and adjusted them to get the current range. I then added another macro so that the CF macro would be run every time there is a change in column M.

This is the CF Macro

Sub ApplyCF()
'
Application.ScreenUpdating = False
CurPos = Selection.Address

cfLRow = Range("M" & Rows.Count).End(xlUp).Row 'Finds the last populated row in Col M
cfRange = Range("M7:M" & cfLRow).Address  'Sets the Range for Col M
    
    Cells.FormatConditions.Delete
    Range(cfRange).Select
    
 'CF for > 9
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(TODAY()>EDATE(M7,9),TRUE,FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
 'CF for > 12
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(TODAY()>+EDATE(M7,12),TRUE,FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
    
'CF for Blank cells
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=M7="""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True

Range(CurPos).Select

End Sub

This is the trigger macro that is placed in the spreadsheet module.

Private Sub Worksheet_Change(ByVal Target As Range)

        If Target.Column = 13 Then
        
            Call ApplyCF
        
        End If

End Sub

If you think this all is necessary we can walk you through the setup steps


Report •

#6
February 14, 2013 at 08:07:02
AlteK,
While I am not sure that Macro's are necessary. I would love the opportunity to learn how to set them up. My concern with setting a specific Row range (i.e $M7:$M254) for the columns is that when additional employees names are added I will have to adjust the row ranges for each column (I have 58 columns G through BK). So setting up a rule that applies to the whole columns rather than specific range of columns would be ideal. An additional challenge is that the certifications (columns) have different periods of validity. Most are 12 months, but some are 36 months, or even 60 months. As it stands right now I am manually typing in the set of three formulas (and making adjustments IAW the certifications period of validity) for each column. To top it off the number of columns may also change as new types of certifications are obtained by the employees. Anything you can suggest to make this process more efficient would be much appreciated. Thank you.

Report •

#7
February 14, 2013 at 09:53:30
If you are trying to eliminate the need for specific rules for specific values, why not put the Certification Period values in a specific row for each column and then reference that cell in your CF formula?

I know this simple example won't apply to your exact situation, but the concept might still work.

Let's say I start with this:

The values I will use as the criteria for the CF are in G1:I1. In other words, these are the values that I will be comparing my data to.

The data that I want to CF is in G3:I4.

I want to CF any values in G3:I4 that are greater than the value in Row 1 of their respective column, as indicted by the asterik.

       G       H       I
1      36      12      60
2
3      37*     11      61*
4      20      13*     59

I can select G3:I4 and apply this single CF Rule to the entire range:

=INDIRECT(ADDRESS(ROW(),COLUMN()))>G$1

In the example above, the CF was applied to G3, H4, and I3.

I hope that helps.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 14, 2013 at 13:39:29
Hi

In terms of setting the range for your CFs you can set an upper limit that you are not likely to achieve, for example M7:M10000. In some tests I've done I've found that applying it to the whole column i.e. M:M adds about 100KB to the size of my file (so, not significant) with no discernible impact on performance (the file I tested on is already larger than 1.5 MB - so quite large).

I'd suggest that for this building phase you set the limits very high and monitor any negative impacts on performance or size. When you have it set up and working the way you want it to then you can revisit the CFs to see if there are any improvements you need to make. Also, at that point, when it is fairly stable i.e. you don't anticipate making any drastic changes, that would be the time to consider transferring your CFs to macros.


Report •

#9
February 14, 2013 at 14:31:36
You can also use Dynamic Named Ranges that will change their length based on how many rows have data in them.

http://www.ozgrid.com/Excel/Dynamic...

However, I have heard of issues when trying to apply CF to Named Ranges, especially ones that might change lengths once the CF is applied.

If you do a Google search on conditional formatting named ranges or similar search strings, you can read up on what others have run into and decide whether it's worth the trouble.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question