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
then select E6 on it's own and add another Conditional Format
No need to apply Format conditions and leave Stop if True unchecked.
In VB editor:
Insert a standard module
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
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"