Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

microsoft excelmicrosoft-excel-2007

I am using Excel 2007 on a Windows machine.

I am attempting to place one data bar and one data icon into a cell under the conditional formatting. The issue is that I don't really want to have data icons or data bars for cells that have dates in the future and I only want to have data icons for dates in the at least one month in the past.

This is what I have:

Excel 2007 Databar issue

This is what I want:

What I want

I am using the EOMONTH function to determine the last day of the month for the conditional formatting calculations. For the data bar the formula is =EOMONTH(Now(), 4) and =EOMONTH(Now(), -1). The data icons formulas are =EOMONTH(Now(), -1) and =EOMONTH(Now(), -2).

Is there a way in Excel 2007 to get rid of the data icons for all the dates in the future and lose the data bars when the date has past?

Best Answer

Well, after many hours searching the internet I was able to find an Office Excel Blog that had my answer. http://blogs.office.com/b/microsoft-excel/archive/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars.aspx

The idea is to provide a formula for each conditional format to tell it which cells to format and which cells not to format. There is no interface for this built-in to excel so we need to turn to VBA to apply the formulas.

selection.FormatConditions(1).formula = "enter formula here"  
    -- Quotes are mandatory and the Conditional Formats start with "1"
       at the top of the list   
    -- If you make a mistake selection.FormatConditions(1).formula = "=TRUE"

Adding formulas to excel conditional formats

I wasn't sure if this format will persist from one session to the next. I decided to open the Excel file and take a look at its XML contents. In the worksheets folder (after you add the .zip extension to the file), click on the spreadsheet "Sheet1" and at the bottom is the information for the conditional formats. The XML on the left is from before I applied the conditional formats formula and the XML on the right is after.

enter image description here

Related Question