+ Reply to Thread
Results 1 to 7 of 7

Conditional formatting to skip every other row & to keep format when moved to other sheet.

  1. #1
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Conditional formatting to skip every other row & to keep format when moved to other sheet.

    Hi everybody.
    I have a "conditional formatting" question.
    In this attached workbook Conditional Formatting.xlsx I have a number of CFs (aka: Conditional Formatting). The colors are very important when keeping track of many orders so I thought that instead of manually changing the row color excel would do it for me when a value is entered into a cell.
    The problem I am having here is two things:

    1) I really need an empty row (empty of CF) between each row that has in it the CF (as I need the empty row to type notes) Is there a formula in the CF that can do that?

    2) It's imperative that when I invoice the customer I move the row to the next sheet by cutting it out of sheet1 and inserting it in sheet two. (The reason I use the insert instead of the paste option is because I would like to insert it in order of date, pasting wouldn't be an option then) The problem with this is that when I cut the row and insert it in the next sheet the CF gets all messed up. The part of the CF that I want to be cleared once I enter data in its respective cell gets un-cleared once I move the row. Is there way have the conditional format change the format so that it would reflect the row's destination when it's being inserted in the next sheet?

    Any help would be greatly appreciated.

    Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    Your data is in odd rows and the blanks are even therefore check if odd
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    Thank you for your response.
    The part of skipping blanks worked like a charm.
    Is there a way for the conditional format to be copied to the next sheet and have the formula change with the process? Like cells in excel spreadsheets, when I enter a formula in the cell and then drag the formula down a few cells the formula in each cell automatically changes to work with the cells.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    I don't think Excel will do what you want. If you insert the copied row instead of pasting it, the conditional format will follow but it will only apply to that row. I think you will still have to manually modify the "applies to" range every time you insert a new row.

  5. #5
    Valued Forum Contributor
    Join Date
    11-27-2013
    Location
    Brooklyn, NY
    MS-Off Ver
    Office 365
    Posts
    1,172

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    Hi protonLeah,
    I hear. Instead would it be possible to write a macro that would cut a selected row from the first sheet and insert it to the second sheet (of course in order of date which is in column "B") and then have the macro change the Conditional Formula to be based off the new row?

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    I'll look at it on Thursday.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,612

    Re: Conditional formatting to skip every other row & to keep format when moved to other sh

    Is there a specific date in B to use?

+ 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] Need help : Conditional formatting border line with skip blank data
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 11:27 AM
  2. Date Value from UserForm moved to Sheet changes Format.
    By msaric in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2013, 07:34 AM
  3. Moved Row to Another Sheet on Conditional Format
    By jojithedevil in forum Excel General
    Replies: 4
    Last Post: 03-11-2013, 07:58 PM
  4. Replies: 3
    Last Post: 04-18-2011, 01:51 PM
  5. Conditional Formatting Code - Deletes Wrong Conditional Format
    By RSpecianJr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2010, 10:53 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