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

User · Answer

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

User · Answer

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

User · Answer

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   amp  Cell Row  amp      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   amp  Cell Row  amp      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   amp  Cell Row  amp      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

User · Answer

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 gt 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

User · Answer

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

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

Examples related to excel

Examples related to formatting

Examples related to conditional

Examples related to formula