+ Reply to Thread
Results 1 to 6 of 6

Conditional Formatting when adding rows

  1. #1
    Registered User
    Join Date
    09-26-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    4

    Conditional Formatting when adding rows

    Alright,
    Here is the problem and what I need. I have a sheet built with a drop down menu and a formula established to change True/False to Yes/No.

    Additionally I have the entire row (A2:G2) turn red when the cell receives a true statement. I need this to continue through to A150 and then regenerate when a row is added in anywhere from A2 to A150 (or more).

    Problem 1: I have to create the conditional formatting for each and every row to turn red when the referenced (I) cell receives a true statement. Is there a way to generate the conditional formatting throughout the work space without writing each row? (i.e. I must reference I2 due to the true/false statement for A2:G2 and then write I3 for A3:G3 and so forth). I have tried to copy and paste the formatting and also used the click and drag option on the highlighted box; however this is only copying the already written code and not adapting it to the respective row.

    Next, I have tried to create the sheet with and without a table. When adding a row, the drop down and True/False codes remain embedded but the conditional formatting for the row turning red is not consistent or remaining with the added row. Referenced above. Code for I3 to turn (A3:G3) red and then I4 for (A3:G3) and so forth. When inserting a row between A3 and A4, the conditional formatting is not copying over to the inserted row upon receipt of a true statement.

    Problem 2: How can I get the conditional formatting to copy over on the inserted row and reference the appropriate cell. (i.e. there is a row added between I3 and I4. The new I4 will keep the reference to the true statement regarded in I4 and then turn A4:G4 red and the new I5 will act in the same manner according to a true statement.

    As a side note, I would prefer to keep it as a sheet and not a table.

    Thanks in advance for any assistance!!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Conditional Formatting when adding rows

    One CF rule for the whole table is enough
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

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

    Re: Conditional Formatting when adding rows

    Mis-read - deleted
    Last edited by protonLeah; 09-26-2014 at 05:27 PM.
    Ben Van Johnson

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional Formatting when adding rows

    To apply a conditional formatting rule to a range of rows or columns, select the entire range and set up the formula as if it's relative to the active cell, the first cell in the range. For my table, since the range is from A2:D8, I select that range and New Rule>Using Formula
    =$D2
    (I want the cells red if there are 3 entries in a row)

    If you set up your data as a table, apply the conditional formatting to the whole table (minus headers), when you add or insert rows, they will keep the conditional formatting.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    09-26-2014
    Location
    Holland
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional Formatting when adding rows

    Dank je wel . Ik lees door je CF en is het zinvol nu .

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional Formatting when adding rows

    Glad it helped.

+ 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. lose range conditional formatting adding/deleting rows
    By mrodenkirch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-29-2013, 12:46 PM
  2. Replies: 3
    Last Post: 05-23-2013, 02:12 AM
  3. [SOLVED] Conditional Formatting (Adding text and cell formatting)
    By fredjones in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-26-2012, 04:15 AM
  4. Adding conditional formatting
    By stewart1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-23-2011, 07:36 AM
  5. Adding Conditional Formatting via VB
    By Snickers in forum Excel General
    Replies: 2
    Last Post: 11-10-2010, 02:39 PM

Tags for this Thread

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