Conditional Formatting cell fill not working

Question

I have a spreadsheet which has text charecters in it. When I set the Conditional Formatting to "Format only cells that contain" the Specific Text containing "Green". I want the fill and font to change to Green. Only the font changes, the fill does not. Any ideas?

Answer 1

I have encountered the same problem with a spreadsheet I exported from TOAD.  A macro I had written for another department and that has been working for years suddenly failed to apply fill colors as a part of conditional formatting.  And not only the macro failed:  I couldn't even apply fill colors manually using the functions on the ribbon.

Copying all the spreadsheet contents and pasting them into a brand new worksheet in a brand new workbook worked but that's a little kludgy. 

Eventually I discovered that if I selected all cells and then applied "No Fill" from the ribbon, I could suddenly fill cells with color.  A similar fix works for borders that won't appear:  select all cells and then choose "No Borders".   Don't know about text color.  It has not failed.  

Anyhow, try marking all cells with "No Fill" and "No Borders" and then retry your conditional formatting.  It works for me so I just added that to my macro before I add the conditional formatting.

Answer 2

Thanks for your response.  I am running Excel on Office 365 and have been using very simple Conditional Formatting for a couple of years.  Recently it stopped working.  What caught my eye about your post was the reference to Access.  The spreadsheet I was working on was exported directly from Access to Excel.  When I copied that spreadsheet and pasted it into a blank spreadsheet the "fill" part of the conditional formatting immediately started working.  My problem was definitely something to do with exporting from Access.  Thanks.

Answer 3

I have the same issue.  I created a speadsheet with 900 lines.  I do a CF on a column and the fill and border attributes seem not to work.

Example;: I want the field to be shaded yellow with dark red text and borders.  The text is dark red but the borders and shading does not appear?

Any advice ?  I am running Office professional plus 2010.

I have run into this issue.  Just out of curiosity, did you export the original data from the results of an Access query?  

I often run Access queries and then export the data to Excel.  Because some of the fields have so many characters, I must select the option to preserve most formatting else it will truncate data.  In the resulting spreadsheet, the conditional format works as you describe.  If I copy and paste the data from the first spreadsheet into a second spreadsheet, CF works fine in the second spreadsheet.  This has worked fine for spreadsheets with only a few thousand rows, if it had a 100K + rows it might be more cumbersome.

Answer 4

It can be done without formulas, just by selecting the options in the user interface.

If that does not work, then there are probably several different conditional formats for the range that conflict and override one another.

Answer 5

Hi Mike,

Tha't exactlly what I did and it stilll does not fill.  I am creating the conditional format for a range of cells (=$C$2:$BI$240).  I do not know if that makes a difference.