how can I copy a conditional formatting in Excel 2010 to other cells, which is based on a other cells content?

Question

I need to copy a formula based conditional formatting to other cells but i have to change the formula for every single cell condition. how can I do a copy of this condition so that the formula changes accordingly as well? in cells, when copy cells with formula referring to other cells, then excel will change the referring cells accordingly. But I don't know how to do the same in conditional formatting so that I don't need to change my conditional formula manually:

condition: =K21+$F22 (the conditional formula which I put in cell K22) condition: =AB21+$F22 (the conditional formula which I need to be in cell AB22)

As I have more than 10000 cells to apply my conditional formatting formula, it's almost impossible to type this formula manually in every single cell. Is there a way to do it automatically?

I don't know VB but I can learn if the solution is there only. But please give me no abbreviation and explain me completely.

Thanks a lot! Shahab

Answer 1

I ran into the same situation where when I copied the formula to another cell the formula was still referencing the cell used in the first formula. To correct this when you set up the rules, select the option "use a formula to determine which cells to format. Then type in the box your formula, for example H23*.25. When you copy the cells down the formulas will change to H24*.25, H25*.25 and so on. Hope this helps.

Answer 2

You can do this in the 'Conditional Formatting' tool in the Home tab of Excel 2010.

Assuming the existing rule is 'Use a formula to dtermine which cells to format':

Edit the existing rule, so that the 'Formula' refers to relative rows and columns (i.e. remove $s), and then in the 'Applies to' box, click the icon to make the sheet current and select the cells you want the formatting to apply to (absolute cell references are ok here), then go back to the tool panel and click Apply.

This will work assuming the relative offsets are appropriate throughout your desired apply-to range.

You can copy conditional formatting from one cell to another or a range using copy and paste-special with formatting only, assuming you do not mind copying the normal formats.

Answer 3

condition: =K21+$F22

That is not a CONDITION. That is a VALUE. A CONDITION, evaluates as a BOOLEAN value (True/False) If True, then the format is applied.

This would be a CONDITION, for instance

condition: =K21+$F22>0

In general, when applying a CF to a range,

1) select the entire range that you want the Conditional FORMAT to be applied to.

2) enter the CONDITION, as it relates to the FIRST ROW of your selection.

The CF accordingly will be applied thru the range.

Answer 4

I, too, have need for this! My situation involves comparing actuals with budget for cost centers, where expenses may have been mis-applied and therefore need to be re-allocated to the correct cost center so as to match how they were budgeted. It is very time consuming to try and scan row-by-row to see if each expense item has been correctly allocated. I decided that I should apply conditional formatting to highlight any cells where the actuals did not match the budget. I set up the conditional formatting to change the background color if the actual amount under the cost center did not match the budgeted amount.

Here's what I did:

Start in cell A1 (or the first cell you want to have the formatting). Open the Conditional Formatting dialogue box and select Apply formatting based on a formula. Then, I wrote a formula to compare one cell to another to see if they match:

=A1=A50

If the contents of cells A1 and A50 are equal, the conditional formatting will be applied. NOTICE: no $$, so the cell references are RELATIVE! Therefore, you can copy the formula from cell A1 and PasteSpecial (format). If you only click on the cells that you reference as you write your conditional formatting formula, the cells are by default locked, so then you wouldn't be able to apply them anywhere else (you would have to write out a new rule for each line- YUK!)

What is really cool about this is that if you insert rows under the conditionally formatted cell, the conditional formatting will be applied to the inserted rows as well!

Something else you could also do with this: Use ISBLANK if the amounts are not going to be exact matches, but you want to see if there are expenses showing up in columns where there are no budgeted amounts (i.e., BLANK) .

This has been a real time-saver for me. Give it a try and enjoy!

Answer 5

I had this problem too and couldn't solve it without using VBA.

In my case I had a table with numbers that I wanted to be formatted and a corresponding table next to it with the desired formatting values.

i.e. While column F contains the values I want to format, the desired formatting for each cell is captured in column Z, expressed as "RED", "AMBER" or "GREEN."

Quick solution below. Manually select the range to which to apply the conditional formatting and then run the macro.

   Sub ConditionalFormatting()
For Each Cell In Selection.Cells
With Cell
    'clean
    .FormatConditions.Delete

    'green rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""GREEN"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False

    'amber rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""AMBER"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    .FormatConditions(1).StopIfTrue = False

    'red rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""RED"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

Next Cell

End Sub