• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

Multiple Conditional Format formula1 inconsistencies

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
RODELS
Asked:
RODELS
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
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
 
RODELSAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
RODELSAuthor Commented:
No comment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now