Re-Initialize Conditional Formatting Array Formulas (50+ Rules/Columns)

[Mesanic]

Hello all!

I have a workbook with multiple tabs (such as "MF1 - Employee Data") that acts as a template for clients to place their employee data into. Also in this workbook is another tab (Validation Data) that contains all of the configuration/build details of the client's environment. The purpose of this workbook is to compare client provided data/values to the configuration/build values and visually indicate for another team at my company to review and either clean up or provide feedback to the client to correct. However... The conditional formatting formulas used to identify the records that don't match are array formulas, which come to find out do not "initialize" or calculate properly when they are added to a range by VBA. If I were to go into the Conditional Formatting Rule Manager and just merely look at the formula, hit enter, and then "Apply" again, it works just fine. So my question is. Does anyone know of a way to force the work book to essentially do those manual steps of going in, "looking at it", and re-applying it?

I've heard alternatives of changing your formulas to not use arrays, but honestly, this is the only way I know how to do them (Index(Match)) and it works extremely well for my purposes. Also I've seen suggestion of just simply dealing with it and going in and doing the manual process, however, I have roughly 50+ columns with conditional formats that will be applied this way and it's far too much to ask members of the team I am delivering this to to do for each client.

I'm hoping someone has a VBA function up their sleeve that can perform this work, because I just don't know what to do. The "prototype" alternative I built where I enter all of the Conditional Formatting rules is also way to much to ask this other team to do for each client's workbook and it required changing all of the ranges for all 50+ rules over and over again every time the data changed. So this VBA is really the most efficient and dynamic way of doing this. In any case, below is the script I've written so far for just the Company Code lookup (CoCode). My fate is in your hands, MrExcel.com!

Sub CF()

Dim ValDataLastRow As Long
ValDataLastRow = Worksheets("Validation Data").Cells(Rows.Count, 1).End(xlUp).Row

Dim MF1LastRow As Integer
MF1LastRow = Worksheets("MF1 - Employee Data").Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox (MF1LastRow)

Dim ColHeaders As Range
Set ColHeaders = Worksheets("MF1 - Employee Data").Range("$A$1:$CD$1")

Dim HdrSearch As String
Dim HdrCol As Integer

'   CoCode
HdrSearch = "CoCode"
HdrCol = WorksheetFunction.Match(Arg1:=HdrSearch, Arg2:=ColHeaders, Arg3:=0)
'MsgBox (HdrCol)

Dim CoCode As Range
Set CoCode = Range(Worksheets("MF1 - Employee Data").Cells(2, HdrCol), Worksheets("MF1 - Employee Data").Cells(MF1LastRow, HdrCol))

Dim Frmla As Variant
Frmla = "=ISNA(MATCH('MF1 - Employee Data'!$C$1&'MF1 - Employee Data'!$C2,'Validation Data'!$C$2:$C$" & ValDataLastRow & "&'Validation Data'!$D$2:$D$" & ValDataLastRow & ",0))"

With CoCode
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:=Frmla
    .FormatConditions(1).Interior.ColorIndex = 39
    .FormatConditions(1).StopIfTrue = False
End With

End Sub

 

[jmacleary]

Hello. Have you tried Application.CalculateFull in your code?

 

[Mesanic]

Hello. Have you tried Application.CalculateFull in your code?

I have tried Application.CalculateFull, Application.CalculateFullRebuild, and I tried recording a macro of me doing it manually and Excel doesn't register in the macro that anything was done.