+ Reply to Thread
Results 1 to 13 of 13

Have blocks of blue colored cells ... want them surrounded by lavender-colored cells

  1. #1
    Registered User
    Join Date
    06-28-2007
    Posts
    39

    Have blocks of blue colored cells ... want them surrounded by lavender-colored cells

    Hi guys. First off I'd like to say that I've cross-referenced here:
    http://www.ozgrid.com/forum/showthre...579#post371579

    I have large data sheets with blocks of blue-colored cells. I want to surround them with lavender-colored cells. Then I want to run a command to Clear Contents of all lavender-colored cells. Is this possible?

    So first I have:
    Black-text
    blue-text
    blue
    blue
    Black-text
    blue-text
    blue
    blue
    Black-text

    Then I want:
    Lavender
    Blue
    Blue
    Blue
    Lavender
    Blue
    Blue
    Blue
    Lavender

    Then I want
    ______ (Clear contents)
    Blue
    Blue
    Blue
    ______
    Blue
    Blue
    Blue
    ______


    I've included a sample worksheet that I did manually to show the progression. I currently run a macro to remove all black-colored cells. Now I need one that will surround blue-text cell ranges with lavender-text cells. And then one that Clear Contents those cells to get my "Final Product"

    Thanks so much.
    Attached Files Attached Files
    Last edited by rocket1406; 06-29-2007 at 03:11 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Rocket1406,

    I have add the macros shown here to your worksheet. The final data appears on a new sheet in the workbook "Sheet1". The data is read from the sheet "Unedited", and the cleaned data is placed on "Sheet1". You can change the names later, just be sure to change them in the macro as well.

    Macro Code:
    Please Login or Register  to view this content.
    The attached workbook has the macros installed. There is a command button on "Unedited" that will run the macro for you.

    Sincerely,
    Leith Ross
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Wow thanks so much.

    I inserted your Macro into the "Unedited" sheet. (Alt + F11, Insert Module "Unedited")

    It gave me an error: Run-time error '9': Subscript out of range.

    Then I added a blank "Sheet1" on the bottom.

    Then I tried running the Macro again.

    It worked for column D and column E.

    Nothing happened for column A and column B. (blank)

    Any ideas? Just need to apply it to column A and B, as well as D and E.

    Thank you so much dude.
    Last edited by rocket1406; 06-30-2007 at 08:12 AM.

  4. #4
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Also, I have a request that could make things easier.

    I'm stupid and yell at me please.

    Instead of me making the cells lavender at the edges of blue regions, and then Clear Contents-ing them later, why don't I just do a Right Click --> Insert --> Shift Cells Down command (for Column A/B (dependent), for Column D/E (dependent))?

    Is this possible to do in either a Keyboard shortcut where I manually select the blue region and run the keyboard shortcut? or is there a macro that can find the start and end of blue regions and do the Right Click --> Insert --> Shift Cells Down command?

    (the lavender procedure is just to get a "space" cell in the final product)

    So do you see what I'm saying:

    On the unedited, suppose it's just black and blue (and red) (no lavender)

    Step 1: (Shift Cells Down)

    Black
    Blue : Right Click --> Insert --> Shift Cells Down
    Blue
    Blue
    Blue
    Black : Right Click --> Insert --> Shift Cells Down

    Step 2: (Remove All Black Cells, Shift Up)

    Black
    "Space" cell
    Blue
    Blue
    Blue
    Blue
    "Space" cell
    Black

    Please Login or Register  to view this content.
    Final Product:

    "Space" cell
    Blue
    Blue
    Blue
    Blue
    "Space" cell

    Is this possible?
    Last edited by VBA Noob; 07-01-2007 at 06:33 AM.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Rocket1406,

    In response to your post about columns "A" and "B" not working, that was my fault. I forgot to move a line of code before I posted. Here is the correct macro code...

    Please Login or Register  to view this content.
    I will work on some code for your post about the black and red separation.

    Sincerely,
    Leith Ross

  6. #6
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Thanks man, yeah that fixed it.

    Now the only thing I need is to know if either you can have it find blocks of blue cells and make 1 above the block (black cell) lavender in color, and 1 below the block (black cell) lavender in color.

    If you can, then that would be all I need!! Right now I have to go through tons of data to find those blue blocks, and then I manually make 1 above lavender, and 1 below lavender. If that could be automated it would be such a time-saver.

    ---

    Either that, or if you could approach this whole thing a little differently and replace that with the Insert shift cells down command at the start and end of blocks of blue cells.

    Both ways will get the job done. Whichever one is easiest for you!!

    I saw this ... I don't understand it but maybe it will help "Color the Neighbors" section:
    http://pubs.logicalexpressions.com/P...cle.asp?ID=407

    You are awesome Thanks so much!

  7. #7
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Basically, the final product that happens as a result of your macro IS EXACTLY what I need ... but that is after I make the cells surrounding blue-cells, lavender by myself.

    I want to know what you think about it ... the whole lavender thing was just an idea I came up with for spacing (Clear Contents later on)

    Do you think the Lavender method is better, or an Insert Shift Cells Down method is better?

    Both would yield the same end result, it's just which way is easier for you!

    THANKS SO MUCH !!!

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Rocket1406,

    Can you explain to me how you determine what cells become lavender? That would help me in providing you with an automated solution.

    Thanks,
    Leith Ross

  9. #9
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    The cells that become lavender are the ones that are before and after a blue block.

    So...

    Before:

    Black
    Blue
    Blue
    Black
    Blue
    Blue
    Black
    Black

    After:

    Lavender
    Blue
    Blue
    Lavender
    Blue
    Blue
    Lavender
    Black

    as you can see, some of the blacks get changed to lavenders.

    Does that clarify?

    I just use lavender for spacing of blue blocks so they don't touch each other.

    The Final Product would be (keep in mind black deletion):

    Space
    Blue
    Blue
    Space
    Blue
    Blue

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Rocket1406,

    Here are the macros to separate the colors with empty cells inserted. This version deletes all the black font cells, and then adds the lines between the blue and red font cells. Replace the code in your Standard Module with the code below...

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Last edited by Leith Ross; 07-01-2007 at 06:34 AM.

  11. #11
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Thank you so much. It is ALMOST there. Wow thanks a lot.

    The last problem (I noticed on a 2nd test sheet) is that the blue blocks are touching each other now.

    I'll show you in the attachment.

    Unedited --> What I run your macro on
    Sheet1 --> Macro results (blue touching)
    What I Need It To Be --> Blue's are spaced from each other (what I need)

    Is there any way to do that? Have your macro directly give me the results on the sheet "What I need it to be" (put a space between the blue blocks)?

    (It works fine in the first test file because there are no blue black blue regions)
    Attached Files Attached Files

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Rocket1406,

    The macro has been revised to provide the separation you wanted in your last post. Replace the macro module with the code below. I added more comments so you can easily see what the macro is doing. The macro SeparateColors has been removed in this code.

    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Last edited by Leith Ross; 07-01-2007 at 02:28 PM.

  13. #13
    Registered User
    Join Date
    06-28-2007
    Posts
    39
    Worked like a charm.

    THANK YOU SO MUCH.

    YOU ARE AWESOME!!!!!

    :-)

    The world needs more nice, helpful people like you. Wow, thanks a lot!!! :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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