MartinS
Active Member
- Joined
- Jun 17, 2003
- Messages
- 490
- Office Version
- 365
- Platform
- Windows
I have a sheet that is used as a template within a workbook, i.e. any new sheets that rely of the layout get created from the template sheet and the formula/conditional formatting get updated by the code.
Once the sheets are created (there may be multiple), the user can specify which previous sheet the default values should come from. If the user then overrides any of these values, the cell text should be red.
The conditional format covers the following ranges:
=$B$14,$J$15:$J$23,$L$15:$N$23,$L$26:$N$33,$L$36:$L$37,$N$36:$N$37,$L$38:$N$39,$J$49:$K$56,$N$49:$N$56,$J$26:$J$33,$J$36:$J$39,$F$14:$F$16,$F$18:$F$20,$F$24:$F$26,$B$28:$B$29,$F$28:$F$29,$F$33:$F$40,$F$42,$D$45:$F$49,$D$52:$F$56,$B$45:$B$49,$B$52:$B$56,$N$43:$N$46
The default formula (applied to B14) is:
The following code stores the details for the relevant formats into an type object and then deletes and re-adds the format.
This code is called when the user specifies the relevant sheet to reference:
When this code runs, and updates the formula to reference the new starting point tab, it's clearly broken:
Stepping through the code, the formula being stored is correct, but it's clear that the last few lines that add the format conditions in are the cause. Is there any way to fix this?
Many thanks
Martin
Once the sheets are created (there may be multiple), the user can specify which previous sheet the default values should come from. If the user then overrides any of these values, the cell text should be red.
The conditional format covers the following ranges:
=$B$14,$J$15:$J$23,$L$15:$N$23,$L$26:$N$33,$L$36:$L$37,$N$36:$N$37,$L$38:$N$39,$J$49:$K$56,$N$49:$N$56,$J$26:$J$33,$J$36:$J$39,$F$14:$F$16,$F$18:$F$20,$F$24:$F$26,$B$28:$B$29,$F$28:$F$29,$F$33:$F$40,$F$42,$D$45:$F$49,$D$52:$F$56,$B$45:$B$49,$B$52:$B$56,$N$43:$N$46
The default formula (applied to B14) is:
Excel Formula:
=AND(NOT(ISBLANK(EAStartingPoint)),B14<>'EA-LVDate Basis'!B14)
This code is called when the user specifies the relevant sheet to reference:
VBA Code:
Dim fcoChange As FormatCondition
Dim fcoNew As FormatCondition
Dim i As Integer
Dim intCount As Integer
Dim strNewFormula As String
Dim arrFormatDetails() As UDT_CondFormatSetting
For i = rngFormats.FormatConditions.Count To 1 Step -1
Set fcoChange = rngFormats.FormatConditions(i)
If fcoChange.Type = xlExpression Then
'Check that formula contains the old start point sheet name, or if referring to tables on the same sheet that it contains
'the starting point range reference
If InStr(1, fcoChange.Formula1, strOldSheet, vbTextCompare) > 0 Or _
(Len(strTableSP) > 0 And InStr(1, fcoChange.Formula1, strTableSP, vbTextCompare) > 0) Then
intCount = intCount + 1
ReDim Preserve arrFormatDetails(1 To intCount)
Set arrFormatDetails(intCount).CF_Range = fcoChange.AppliesTo
strNewFormula = Replace(fcoChange.Formula1, strOldSheet, strNewSheet, , , vbTextCompare)
arrFormatDetails(intCount).CF_Formula = strNewFormula
'MS BUG in modify method changes formula references, so store details and delete to re-add later
arrFormatDetails(intCount).CF_Type = fcoChange.Type
arrFormatDetails(intCount).CF_FontColor = fcoChange.Font.Color
fcoChange.Delete
End If
End If
Next i
If intCount > 0 Then
For i = 1 To UBound(arrFormatDetails) Step 1
Set fcoNew = arrFormatDetails(i).CF_Range.FormatConditions.Add(arrFormatDetails(i).CF_Type, ,arrFormatDetails(i).CF_Formula)
fcoNew.Font.Color = arrFormatDetails(i).CF_FontColor
Next i
End If
Excel Formula:
=AND(NOT(ISBLANK(EAStartingPoint)),XEV13<>'EA-Tech Provisions A'!XEV13)
Many thanks
Martin