RODELS
asked on
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
As far as I can see the only way round it is to apply the first rule to E6 separately from F6:J6.
ASKER
No comment
ASKER
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