+ Reply to Thread
Results 1 to 7 of 7

Change cell fill color of specific, conditionally formatted cells w/o changing format

  1. #1
    Registered User
    Join Date
    12-08-2011
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Change cell fill color of specific, conditionally formatted cells w/o changing format

    Sorry if this has been covered; I searched the forums and Google but could not find an applicable answer.

    I have conditionally formatted cells on a spreadsheet so it is comprised of rows and columns of alternating colors. Here's a screenshot to demonstrate what it looks like:

    excel 1.jpg

    The conditional formatting is applied to all Columns to the right of and including Column G. Before the conditional formatting, I had applied the cell coloring by simply using the Fill Color button but found that when I added new rows/columns, the colors no longer alternated correctly. This prompted me to change to the conditional formatting option. The issue I am having now is that prior to applying the conditional formatting, I had been coloring in specific cells to denote different information. For example, in cells O132 - Q132 in the screenshot above, when I would receive a payment for that specific fee, I would apply a green fill via the Fill Color button to the cells to show that payment was not longer outstanding. Now that I have used the conditional formatting to control the general pattern for cell fill colors, I can't use the Fill Color button to alter the fill color of the affected cells. Is there a simple way that I can keep the cells conditionally formatted to display the color pattern but still allow the format to be overridden in specific instances? If this is not an option, is there a different way to apply a set alternating color scheme to the spreadsheet in general so it holds the pattern when new rows/columns are added but still allow for specific cells' colors to be easily changed?

    Hopefully I have explained this clearly. If anyone needs clarification on anything, please let me know. Thanks for any help you can give.

  2. #2
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    Hi You could highlight all the cells you are using then insert a table using highlighted cells as the data for the table. you would then get alternating colour scheme which keeps its integrity when you insert new rows.

  3. #3
    Registered User
    Join Date
    12-08-2011
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    Hey thanks for the quick response. I tried your solution and made the table by highlighting the conditionally formatted cells. I found that while it does maintain the color scheme when new rows/columns are added, I am still unable to change specific cells' color by using the Fill Color button. Is there a step I missed? Like I said, the only thing I did at this point was create a table with the cells to the right of and including Column G.

  4. #4
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    Hi

    Have just looked at a sample I put together and was able to insert fill colours and have conditional formatting in the same cell.. have attached my sample for you to mess about with
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-08-2011
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    OK I checked out your sample. The difference between your sample and what I am trying to do is that you used the table format option to dictate the fill color of the cells in your table. I am using the conditional formatting to dictate the fill color of the cells. If you look at the screenshot I attached in the original post, you can see that it is not a simple recurring pattern which the table format seems to allow you to create but rather a somewhat random color scheme. From looking at the options in the Table Format, I do not believe that I can use that function to duplicate the color pattern that I created using the conditional formatting. This then brings me back to the original problem - is it possible for me to use conditional formatting to create a background color scheme for all the cells but still be able to change the fill color of specific, random cells as needed? Anyone?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    i would suggest you upload a sample work book (and not a pic), (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    12-08-2011
    Location
    PA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Change cell fill color of specific, conditionally formatted cells w/o changing format

    OK good call. I did attach a modified version of the spreadsheet I'm working with with some of the info deleted. If you open it and open the conditional formatting manager, you can see how I've used this function to create the background color scheme for cells in and to the right of Column G. What I want to be able to do is change the colors of specific cells. If you look at F15 and then F16, you can see how I put in a date when I mail out paperwork (F15) and then once I receive a reply to that paperwork, I color the cell green (F16). I want to do the same over in the cells which are affected by the conditional format. For example, cells O15, P15 and Q15 - I enter that info in when I send out a bill. When the bill is paid, I want to be able to simply color those 3 cells green. With the conditional formatting, I am not able to use the Fill Color button to change the cells' color; the Fill Color button will not override the conditional formatting. To restate my goal: I am trying to find a simple way to have the background of the cells colored as I have it and remain in that pattern when a new row or column is added. (Before, I used the Fill Color button to create the background scheme and found that when I added new rows or columns, the pattern would not hold and I ended up with a messy looking worksheet. This prompted me to use the conditional formatting to avoid this problem.) Additionally, I need to be able to easily change the color of specific cells as needed. I don't want to have to go into conditional formatting every time I need to change a specific cell color as this is too cumbersome and time consuming for the amount of times I will need to recolor a cell - I need a more convenient method. Any ideas are appreciated. Let me know if you need clarification on anything. Thanks
    Attached Files Attached Files

+ 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