Condtional Formatting - Inserting Rows

My conditional formatting applies to cells (see row numbers):

=$B$41
=$B$42


When I insert 10 rows between row 41 and 42, the condition now applies to 
cells as follows:

=$B$41:$B$51

1. Is there a way that the condtions will not be copied to rows 42 to 51? 
Or, the condition will apply only to each cell, i.e."


=$B$41
=$B$42
=$B$43
etc. to =$B$51

Thank you.



0
Utf
12/23/2009 7:16:03 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1125 Views

Similar Articles

[PageSpeed] 45

Use the absolute column reference of $B  and the relative row reference of
41  to get the seconf optiont.

=$B41

To not have the CF copied to the inserted rows, turn off the option to
"extend data range formats and formulas"


Gord Dibben  MS Excel MVP


On Wed, 23 Dec 2009 11:16:03 -0800, Danny <Danny@discussions.microsoft.com>
wrote:

>My conditional formatting applies to cells (see row numbers):
>
>=$B$41
>=$B$42
>
>
>When I insert 10 rows between row 41 and 42, the condition now applies to 
>cells as follows:
>
>=$B$41:$B$51
>
>1. Is there a way that the condtions will not be copied to rows 42 to 51? 
>Or, the condition will apply only to each cell, i.e."
>
>
>=$B$41
>=$B$42
>=$B$43
>etc. to =$B$51
>
>Thank you.
>
>

0
Gord
12/23/2009 8:24:07 PM
Reply:

Similar Artilces:

PowerPoint 2007-Formatting Hyperlinks
How do you change the color of hyperlinks in 2007? This is a multi-part message in MIME format. ------=_NextPart_000_0007_01CAC6B8.89E7EA80 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable Have a look here www.pptfaq.com/FAQ00546.htm=20 --=20 Michael Koerner MS MVP - PowerPoint "SueW" <SueW@discussions.microsoft.com> wrote in message = news:FBFA1989-C534-478C-B992-A27696F7DFC7@microsoft.com... > How do you change the color of hyperlinks in 2007? ------=_NextPart_000_0007_01CAC6B8.89E7EA80 Content-Ty...

anyone know where to set the transaction number format?
Hi, Deos anyone know where to set the transaction number format. I need to set the transaction number from 7 digit to 6 digit. regards Dennis Dennis, The Transaction number started with 1. Are you saying that you have used a million numbers already? SO Administrator | File | Connect | put in your password | Database | Set next Transaction Number -- * "Dennis" <Dennis@discussions.microsoft.com> wrote in message news:DB499B32-9A24-4090-896E-DF0644467DCC@microsoft.com... Hi, Deos anyone know where to set the transaction number format. I need to set the transaction numb...

legend formatting
Is there any way I can have subscript/superscripts in the legend of a chart? If it's not readily available, can a VBA script take care of it? Maynard - Unfortunately it is not possible to format individual characters of a legend entry or an axis tick label. You could make a textbox, which allows individual character formatting. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Maynard wrote: > Is there any way I can have subscript/superscripts in the legend of a > chart? If it's not readily available, c...

Add row between current rows in a way to long sheet....
I need to add 1 row between each current row in an 2670 rows long excelsheet. And yes I am lazy, so could anyone help me create the macro? Seems I'm doing something wrong... :( PS: I'm working excel 2003. Thank you for your help! Just fill a simple sequence 1, 2, 3, ... 2670 in a blank column next to your data (imagine this is column H). Then copy that sequence down to H2671 and down. Then highlight the block of data (including the helper column) down to row 5340 and perform your sort, using column H as the sort key. You can delete column H afterwards. Hope this helps. Pete On...

move down one row
I'm trying to write a macro that goes to the first blank cell at the end of a column. I found Selection.End(xlDown).select this gets me to the end of the list but I can't figure out what to add to move down 1 row. If there is another way to do the same thing please tell me. Thanks in advance for the help ActiveCell.Offset(1, 0).Range("A1").Select Will move down 1 cell. "Truseeker" <truseeker@comcast.net> wrote in message news:2o6dnSs2_5oGJaTfRVn-pg@giganews.com... > I'm trying to write a macro that goes to the first blank cell at the end...

Auto Hide/Unhide Formatting
I'm wanting to create conditional formatting such that when users submit info in one cell, a previously hidden row underneath it becomes unhidden and available for formatting. Is there a way to do that? Not using Conditional Formatting. You would need event code to achieve the unhiding of a row. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False If Target.Value <> "" Then Rows("2:2").EntireRow.Hidden = False End If endit: Applic...

How do I set up the default format for a comment?
I wan't all new excel comments to have the same look by default without having to edit each comment individually. I'm using Office 2003. I thought all the comments would have the same look. But if they don't, Debra Dalgleish has lots of code to format comments when you add them: http://www.contextures.com/xlcomments03.html#Formatted http://www.contextures.com/xlcomments03.html#Plain Gitesh wrote: > > I wan't all new excel comments to have the same look by default without > having to edit each comment individually. I'm using Office 2003. -- Dave Peterson ...

Forumla rows and columns
Why in a column do I get a (0) result from a formula in one cell in the column and get a - for a result in the same column, different row with the same formula for each, the format for the entire column is accounting. I want all zero results in the column to be displayed as a dash (two results are (0) in the column and two other results to the same forumla show as the dash which is the correct format for accounting) -- Help !!!! The formula result is between 0.5 and -0.5. Formatting does not alter the real value. Wrap ROUND around your formula: =ROUND(your_formula_here,0) ...

rows delete via macro needs too much time
Hello all, I wrote a macro which have to delete some rows in a sheet. The sheet contains many sum formulas. When I start the macro, it work very well but after 10 deletes it needs more time than before and after 20 deletes it needs over 30 seconds for one delete. When I break the macro and do the delete manually, the same happen. Post the code. Also, have you tried turning off screenupdating and calculation whilst it is running, and then putting it back on at the end:- Sub xyz() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Code................ ...

Excel 2007: Conditional formatting
In Excel 2007, I want to create a rule that turns cells red in case that 3 out of 7 cells in a column equal 0. What formula will catch this condition? Thanks to the math stars! Sven I did this with A1:G1 Selected the range; used Conditional Formatting on the Home group ->New Rule ->Use a formula ...... and entered this =COUNT($A$1:$G$1,0)>=3 When three or more cells in the range are zero (blanks do not count as zero here), all cells go red best wishes from Canada -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Sven Berg" <...

Using a data field twice in the 'Rows' section of a pivot table.
In my source data, I have a 'Date' field. In my pivot table, I want to use the 'Date' field twice: * Once to sort by 'Year' (i.e. simply changing the format to 'YYYY') * And a second time to sort by 'Month' (i.e. simply changing the format to 'MMMM') Can I do this without the requirement of adding fields to my source data? Thanks. Is this a row field? How about just grouping them by year and month? rightclick on the date field and choose group and show detail|group by months and years. cdavidson wrote: > > In my source data,...

help with header row format
Hi, The first row in my excel file is the header. When I insert a new row after that, the new row takes the format, color etc of the header. How can I avoid the new rows inserted form taking the format of the forst row? Thanks If it's Excel 2003, when you insert the new row under the header row youy should get a small icon appearing which has a drop down arrow providing format options. You can select to format the new row as the line above, the line below, or clear formatting. -- Noel O'K "Chris" wrote: > Hi, > The first row in my excel file is the header. W...

formating within formula
Hi group. In C10 cell I have the following formula: ="The total percentage is " & A10 Cell A10 is a percentage calculation. The problem I have is that in Cell C10, the display is something like this: The total percentage is 0.63447676223746 I know that if this value was a currency, I could have used the formula ="the total percentage is " & dollar(A10). This would have gotten rid of extra decimals. What other formating can I use to only keep two digit in this percentage within the formula? Any help is appreciated Thanks Hi try ="The total percenta...

Format Painter in MS Word
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Hi - How do I set up a keyboard short-cut for Format Painter? (MS Word 2008 for Mac version 12.1.7) Thanks Derek Go into Tools> Customize Keyboard & Menus then click the Keyboard button. On the Commands page select Edit from the Categories list then locate the following commands & assign the keystroke of choice to each: CopyFormat PasteFormat HTH |:>) Bob Jones [MVP] Office:Mac On 5/12/09 10:44 AM, in article 59b744e3.-1@webcrossing.caR9absDaxw, "Derek100@officeformac.com" <Derek100@o...

Link; clipboard format
I would like to extract the executable file name from an icon on the desktop. I've copied a link to the clipboard but it seems to have a non-standard clipboard format. Does anyone know how I can do this or where info on this format might be? thanks It is a shell clipboard format, so you have to read about the shell extensions. There is a special clipboard format in the shell for this. It has been enough months since I did this that I am at this point fairly clueless (I have decided that Shell programming, like ActiveX, is a Place I Do Not Wish To Go). But perhaps someone who understands...

Printing Reports: Force A4 Format 07-19-07
hello! i am having trouble forcing a report to be in the A4 format when sending to the printer. it happens all the time that the report get send with the format 'letter' which causes the printer to stop and request for paper in letter format. the report i generate totally fits in the A4 format but it won't work the way i want it to! this is pretty annoying because i always have to manually push a button on the printer so that the report can be printed. is there a way through any preferences or vba code to make this work ? maybe someone can help me or give me a hint. thanks in adv...

Format of SSN Field on Form
I have a table with an pkEmpID (auto number) and txtSSN, txtLName, etc. fields. I have a form with a combo box used to query this table. The combo box retrieves the pkEmpID and txtSSN when the user types in the SSN. (The pkEmpID is bound to a field in another table.) When I type in the SSN, I want the combo box to display it as @@@-@@-@@@@. If I set the field format to @@@-@@-@@@@ in the table, the SSN displays the data okay on the form. But you have to type in the dashes as part of the entry. I prefer not to have to do that. I took the formatting off the table field and tried to s...

code for new rows and filling down
I have the following piece of code that inserts a new row in a given position (the active cell row in current worksheet) on each of the 8 listed worksheets below. When the new row is inserted, the code then 'pulls down' formulae from the above row - this is where the problem lies, i only want certain cells to copy down from above, eg where there is a formulae that needs to be copied down, i do not want manual entry cells to copy actual data down from above. How can i specify which specific cells to copy down? OR Is there a method by which i can fill down - as the coding pres...

How to insert a new field which is not currently in CRM's db
How to insert a new field which is not currently in CRM's db ? KC KC, The implementation guide covers this extensivlely, but you basically do this via the Deployment manger from the CRM app server. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Thu, 22 Jul 2004 14:57:06 -0700, "KC" <kclai@addonsolutions.com.sg> wrote: How to insert a new field which is not currently in CRM's db ? KC ...

Reprint Large format
Does anyone know how I can reprint a Receipt into a different style. I printed a receipt through a 40 column printer and receipt printer two has a 80 column printer that printed the large format receipt i want... I would like to be able to print that large format receipt one more time. You have to have Printer #2 set to Journal Receipts ( in Register Properties). If this is not set, you won't be able to reprint. If it is, you can just open the Journal viewer and page up/page down to find the version you want. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@...

multiple font formatting to a single cell
Hello, I am trying to use superscript in a cell to just one number not the entire text in that cell. I can format it ok, and the formatting is there. However, once I hit the enter key, the formatting to the one number disappears. I'm new to xp's version of excel. I used to be able to do it in earlier versions of excel. Example: salary 2. I only want to add superscript to the number 2. Is there a way to do this in xp? Thanks ...

How can I insert an Adobe Photoshop image into Publisher 2003?
I have an image created in Photoshop that is saved with a transparent background. If I save it as a jpg or tiff, it automatically saves everything on a white background. I am trying to overlay this image onto a gradient color background and want that gradient to show thru behind my graphic. Is this possible? Gary <Gary@discussions.microsoft.com> was very recently heard to utter: > I have an image created in Photoshop that is saved with a transparent > background. If I save it as a jpg or tiff, it automatically saves > everything on a white background. I am trying to overlay...

Field validation and formatting
How do I validate and format a field? I want to format all phone numbers to a standard format--(###) ###-#### x###. However, for standard text boxes, I can not find any event tabs. All I can find is the onchange evend for pick lists. Thanks in advance! I saw this discussed several times that its not modifable. We run CRM in house but im not the CRM admin so I never verified it I just took the answer for what its worth. "Paul McBride" <pjmcb@comcast.net> wrote in news:#ECB0l9hEHA.1096@TK2MSFTNGP10.phx.gbl: > How do I validate and format a field? I want to format all...

Conditional formatting 02-11-10
Hi In a cell, I want to allow the value only if a condition is satisfied. If this condition is not satisfied, and if I format the value to be white, then the value is still in the cell and cannot been seen because it is white. This is no good. I want no value in the cell. Any help Louis You can use Data Validation to prevent the entry of an invalid value. Or you could use event code to delete the invalid value after entry. Which way to go is up to you. Gord Dibben MS Excel MVP On Thu, 11 Feb 2010 07:35:01 -0800, LP <LP@discussions.microsoft.com> wrote: ...

Conditional Formatting in Excel Help Please.....
Newbie to VBA I need to use Conditional Formatting to format a range of cells to have Wheat background and Red text (Bold) if a text string is contained in each cell. For example, all cells in a range that contain the text string "LLC" or "Inc" or "Corp" or "Corporation" or "Company" , etc. If cell E2 contains Acme, LLC I need to give that cell (E2) the condition formatting If cell E3 contains John Brown That cell does not get the condition formatting If cell E4 contains Joe's Pool Hall, Inc Cell E4 gets the condition formatting Thanks ...