Solved

Multiple Conditional Format formula1 inconsistencies

Posted on 2011-03-16
4
416 Views
Last Modified: 2012-05-11
Hello all,
Please can someone assist or shed some light on this issue?

If a cell has multiple CF's and the appliesto ranges overlap, the .formatconditions.formula1 returns the same formula for all CF's.  It's best explained if you do the following:

In a new workbook Sheet1 do the following:
A1 - Name as expenses_type
A1 enter "labour"  -- no " obviously
 cells E3 = "Type 1",- drag to J3 which should = "Type 6"
Cells E4:J4 = "money"  -- no " obviously

Select E6:J6 and add conditional format
=expenses_type="money"

then select E6 on it's own and add another Conditional Format
=e6="money"

No need to apply Format conditions and leave Stop if True unchecked.

Select E6

In VB editor:
Insert a standard module
Sub testcf()
Dim iCFCount As Integer, iCnt As Integer
Dim sCForms() As String
iCFCount = ActiveCell.FormatConditions.Count

ReDim sCForms(1 To iCFCount)

For iCnt = 1 To UBound(sCForms)
Debug.Print "CF Item " & iCnt & " is " & ActiveCell.FormatConditions.Item(iCnt).Formula1
Next iCnt

End Sub

Open in new window


In the immediate window you will see:

CF Item 1 is =expenses_type="money"
CF Item 2 is =expenses_type="money"

whereas in reality, CF Item 2 SHOULD = =e6="money"

Please help!  

Kind regards,
Robert
0
Comment
Question by:RODELS
  • 2
  • 2
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35146701
Isn't this the same question you already asked here: http:/Q_26882099.html?

It appears to be a bug, and I can't see any way round it currently.

I wil check it out with the MVP group and file it as a bug if it hasn't already been reported.
0
 

Author Comment

by:RODELS
ID: 35146730
Hi yes it is the same question but there has been no definitive statement about this being a bug or not and no comments have been added since Sunday.  The last comment was a restatement of my question.

If it is a bug, it's been around for ages because it does this in 2007 (and I believe in 2003 where there was a workaround) ... That workaround no longer works so I was wondering if there is a workaround the workaround.

Many thanlks for looking at this.

Kind regards,
Robert
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35146770
It does not behave the same for 2003 (at least for me), but it does in 2007 and 2010 (not that surprising since the CF changed in 2007)
As far as I can see the only way round it is to apply the first rule to E6 separately from F6:J6.
0
 

Author Closing Comment

by:RODELS
ID: 35292003
No comment
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Name Selection and Sorting 43 91
Compare Excel spreadsheet - 1 3 37
Formula returning #N/A 9 31
Excel Total at footer of invoice 5 17
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question