VBA or Built In? Match Status with Expected values - highlight cells

[Rx_]

See attachment

There are about 20,000 sites (1 record per site). Each site has a Status.
Each Site, Status record has about 15 columns.
Each of the cells in the 15 columns have a True / False.
The True / False is based on a complex set of business rules that are Functions in SQL Server. The functions have multiple parameters and multiple logic with a return of True or False.

The top row is the Status expected results (T, F, N) for that status column to pass.
The T expects a True below, the F expects a False below, while the N is neither (e.g. it doesn't matter).

All of this is automated from Access with vba code, SQL Server linked tables, and Excel Object VBA.

Objective:
The yellow iin the attachment is just for demo purposes.
For Each Row, for Each Status, take the Expected columns (T,F,N)
Take each Status - if the T matches a True or if F matches a False - then turn the row/column backgound Green. Else turn backgound Red.
The N stay White regardless of True/False in the row (they don't matter).

Question:
My plan is to have Access use VBA to loop through each record, look up the Status, then color each cell according to the Expected Status.
(see code example). It could be the 20,000 rows X 15 columns.

Excel built-in formulas run much faster than VBA code.
Can anyone suggest a solution to use built in Excel functions?
For example: Filter each Status - then apply a Conditional Format to each column.

This is the code I will modify to include Status then format based on the Expected Status. It will have additional loops of course. But in theory will be based on this.

'2930    With objxl.ActiveWorkbook.ActiveSheet
'          'objxl.ActiveWorkbook.ActiveSheet
'2940      For i = intRowPos To intMaxRecordCount + intRowPos
'2950          If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
'2960              .Range(.Cells(i, "B"), .Cells(i, "E")).Font.FontStyle = "Bold"
'2970              '.Cells(i, 33).Value = .Cells(i, 3).Value
'2980          Else
'2990               .Range(.Cells(i, "B"), .Cells(i, "E")).Font.ColorIndex = 16 'metalic gray
'3000          End If
'3010      Next i
'3020  End With

 

[namliam]

while the N is neither (e.g. it doesn't matter).

Not that you can do much about it, but I would expect A for ALL... Neither suggests both are wrong, All suggests all are correct... atleast is my little world

Why so hard? why not use conditonal formatting to do the job for you?
To give you an idea... something along the lines of

    Range("A3:H10").Select 'This will need adjusting to your own applicable range
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A$1=""T""; A3=TRUE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(A$1=""T""; A3=FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A$1=""N"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10498160
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[Rx_]

You are exactly on target!
I have about 300,000 cells that would require the custom Conditional Formatting.
My example only shows 1 of 22 Status. So, each column would have to contain a conditional format for the matching Expected Values Status for that row's Status.

The look-up table for the 22 Status and associated Expected Values in on Sheet 2 in a Named Range.

I am up to coding that. Here is my concern.

The end product during presentation will be filtered by columns.
Will the screen refresh have delays if this many Conditional Formats are applied?

If I use the slower method of external VBA with remote automation to color the cells during the Excel report generation, the filterby color or other filters tend to run very fast.

The end goal is to have color coded results the end user can dynamically filter to identify subset conditions. One a worksheet with no VBA macros.

Hope that explains my decision point.
The N (neither True or False applies) LOL - I am dealing with Government Regulators.
For one Status - a rule applies with an expectation. One rule (R_68) expects a T for one Status, for a different Status it expected a F. For other status it can be either T or F. When it comes to Rules, the output is T or F.
Question: Has someone made a payment to a specific category if a payment is associated with a Federal only Right Of Way and they have a Federal Permit?
Answer: True or False
Expected result: If the Status is New Application, Neither TorF matters. If Status is Under New Permit Consideration then a False is expected. If Status is Permit Completed then True is expected.
I did try to sell a return value of T/F or Null. They couldn't wrap the logic aound null.
So, I changed it to Neither. So, present the fact - it exist or not - then evaluate it according to the Status. Therefore, the N is really the Neural Net Logic interface?

Thanks so much for your valued input. There are not many people that can offer advice on this subject.

Here is one example of an article that is driving my decision:

 [LEFT][COLOR=#333333]There are a number of things that can cause an Excel spreadsheet to slow down. [/COLOR]
[COLOR=#333333]Sometimes, the spreadsheet can become unusable, [/COLOR]
[COLOR=#333333]requiring up to a minute for saving or even the most basic of changes. [/COLOR]
[COLOR=#333333]Here’s a brief checklist of the most frequent causes of Excel slowdowns.[/COLOR][/LEFT]

[LEFT][COLOR=#333333]Conditional formatting can be a processor-intensive task if your rules aren’t set up right. [/COLOR]
[COLOR=#333333]Manage your conditional formatting rules (2007) via the ribbon at Home > Conditional Formatting > Manage Rules… [/COLOR]
[COLOR=#333333]Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column)[/COLOR][/LEFT]

 

[Rx_]

http://www.mrexcel.com/forum/excel-...cells-each-custom-conditional-formatting.html

For what it is worth, one of the MVP at Mr Excel suggested that applying the filter formual to this large amount of Excel cells would not be a good idea.

Thought that might be useful information given his expertiese.
There are not many places or people on the Internet with this level of information about Excel.