+ Reply to Thread
Results 1 to 11 of 11

Inserting a space between lines in conditional formatting

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Inserting a space between lines in conditional formatting

    Hello everybody,

    I need to create a spreadsheet that has approximately 1000 rows with the same exact information in each of them prefilled (as I use the "drag-down" method). I use this chart throughout the year to enter various bits of data in each row. I also use conditional formatting in each row. The company I work for wants to have an empty space between each row.
    I found half of the solution to my problem through the following post I've seen somewhere:

    "You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows."

    This works great for the data that I copied (with the drag down method) to all the rows. However, this method does not insert an empty line in between all the conditional formatting I have throughout the spreadsheet. This is my conditional formatting formula I have "manage rules" which is under "conditional formatting" =NOT(ISBLANK($A6)) (*please take note that this is selected for 1000 rows.) Maybe there is some kind of change in the formula that I need in order for the conditional formatting method to be copied onto every 2nd line of the 1000 rows"
    Please, if any one can help me with this it will be most appreciated!

    Thank you.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a space between lines in conditional formatting

    One of the problems (that I see anyways, is the use of ISBLANK(), this should properly be renamed ISEMPTY, as a cell containing a formula is NOT considered "Blank", even if it returns a value of "", a better formula would be:
    =NOT($A6="")
    or
    =$A6<>""

    Another possible problem is, is the CF applied with the original A column, or is it modified to Bcolumn, then when A column is deleted, it changes to $A6? if the first, the CF probably never happens due to a #REF! error

    Hope this helps

    EDIT-
    Just seen, to the LEFT, so new column a exists, but if cf is applied with original A column, you will still get That error in the CF Formula (check CF,Manage Rules, that rule, Edit Rule, it will show there)
    Last edited by dredwolf; 12-05-2013 at 11:24 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Inserting a space between lines in conditional formatting

    Thanks for responding. I tried all your suggestions but i'm afraid non worked Maybe if I explain what i'm trying to do it might help. I want that once I enter anywhere in the 'A" column some data, that the entire row will automatically turn red. What I also want is that the row underneath it will not have that formula at all, meaning that the formula should repeat itself only every second row. Is this at all possible?

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a space between lines in conditional formatting

    you cannot do that with conditional formatting, it applies or it does not (this is a simplistic explanation, but serves), maybe a small sample, and please include a before and after (and a sheet or 2 with the Interim steps shown as well), it sounds to me as if the second problem is your problem, which means you may need to insert another row,before any additional values are added, then hide that, then use an offset or index formula to get the information for the CF formula to work on, but without seeing how your information is set up and changed, I cannot offer a solid solution for this...

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a space between lines in conditional formatting

    This may work better with a VBA macro actually...have to think about it

  6. #6
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Inserting a space between lines in conditional formatting

    Thanks for showing me how to attach a file. Here is what i'm starting off with TEST 1.xlsx Please take note at the CF by column "A" and column "D".
    Now here is what I want to end up with TEST 2.xlsx*I inserted those spaces manually which took some time, imagine how long it would take me to do one thousand lines, or maybe even more (depends how much my company wants)!
    Thanks for your efforts
    Last edited by kosherboy; 12-06-2013 at 12:44 PM. Reason: Not sure if i sent it correctly

  7. #7
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Inserting a space between lines in conditional formatting

    Thanks for showing me how to attach a file. Here is what i'm starting off with TEST 1.xlsx Please take note at the CF by column "A" and column "D".
    Now here is what I want to end up with TEST 2.xlsx *I inserted those spaces manually which took some time, imagine how long it would take me to do one thousand lines, or maybe even more (depends how much my company wants)!
    Thanks for your efforts

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a space between lines in conditional formatting

    If the blank rows remain blank, then I don't understand what the problem is with the Conditional Formatting, either the existing or my suggested alternatives, they both highlight the rows according to your specs ?
    in your upload (which I assume is showing expected result), I apply my suggestions and the result is exactly the same

  9. #9
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Inserting a space between lines in conditional formatting

    YIPPEE! I figured it out! It's such a simple trick but so hard to think of. All you have to do is have data on one row, select that row and the row underneath which should be blank (and make sure there are no more rows beneath that have any data whatsoever) then left click on the Fill Handle (small black square bottom right of the Selection) and drag down to the end of where you want your data to stop and WALLAH!! You have an empty space between each line which got input into the CF!

    Thanks, Dredwolf, for all your efforts - most appreciated.

  10. #10
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Waterbury, CT
    MS-Off Ver
    Office 365
    Posts
    1,175

    Re: Inserting a space between lines in conditional formatting

    YIPPEE! I figured it out! It's such a simple trick but so hard to think of. All you have to do is have data on one row, select that row and the row underneath which should be blank (and make sure there are no more rows beneath that have any data whatsoever) then left click on the Fill Handle (small black square bottom right of the Selection) and drag down to the end of where you want your data to stop and WALLAH!! You have an empty space between each line which got input into the CF!

    Thanks, Dredwolf, for all your efforts - most appreciated.

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a space between lines in conditional formatting

    You are welcome , but I think you have over-complicated the solution, or, understated the problem, but, if you are happy, so am I

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formatting cells or inserting lines in a protected sheet
    By keithmorphett in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-14-2012, 02:39 PM
  2. Conditional formatting - space for editing rule
    By nsv in forum Excel General
    Replies: 1
    Last Post: 11-07-2011, 10:09 AM
  3. Conditional formatting double space intermittently working
    By RefillMyInk in forum Excel General
    Replies: 1
    Last Post: 08-24-2011, 11:59 AM
  4. Inserting Lines by macro and conditional formatting
    By DrSteve in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-28-2011, 08:17 AM
  5. Conditional Inserting lines
    By GMOODY in forum Excel General
    Replies: 1
    Last Post: 05-26-2010, 10:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1