anyone coding for conditional formatting?

[neemi]

I was just wondering what peoples experience with coding the conditional formatting is like?

My main question is that I have managed to code my criteria for the CF but I am not sure how to set the default settings if any of the Conditions are not met?

Do I just set this as the text box properties rather than as a CF?

Also is there a better way to set my CF rather than what I have done below so that it is more efficient/quicker to run?

Private Sub ApplyCF()
Dim i As Integer
Dim objFrc As FormatCondition

'// ===================================================================================
'// setup CF for person based on Wk1
'// ===================================================================================
    Me.fsbResourceDetails.Form("persName").FormatConditions.Delete
    
    Set objFrc = Me.fsbResourceDetails("persName").FormatConditions.Add(acExpression, , "nz([total_TotWk1],0)<" & GetCFWkMin())
    Set objFrc = Me.fsbResourceDetails("persName").FormatConditions.Add(acExpression, , "nz([total_TotWk1],0)>" & GetCFWkMax())
        
    With Me.fsbResourceDetails("persName").FormatConditions(0)
        .BackColor = 16777164
        .ForeColor = 0
        .FontBold = True
    End With
    With Me.fsbResourceDetails("persName").FormatConditions(1)
        .BackColor = 255
        .ForeColor = 0
        .FontBold = True
    End With

    
    For i = 1 To 12

    '// ===================================================================================
    '// setup wk view CF
    '// ===================================================================================
        Me.fsbResourceDetails.Form("Col" & i & "_W").FormatConditions.Delete
        
        Set objFrc = Me.fsbResourceDetails("Col" & i & "_W").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_W").ControlSource & "],0)<" & GetCFWkMin())
        Set objFrc = Me.fsbResourceDetails("Col" & i & "_W").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_W").ControlSource & "],0)>" & GetCFWkMax())
            
        With Me.fsbResourceDetails("Col" & i & "_W").FormatConditions(0)
            .BackColor = 16777164
            .ForeColor = 0
            .FontBold = True
        End With
        With Me.fsbResourceDetails("Col" & i & "_W").FormatConditions(1)
            .BackColor = 255
            .ForeColor = 0
            .FontBold = True
        End With
    
    '// ===================================================================================
    '// set up day view CF
   '// ===================================================================================
        Me.fsbResourceDetails.Form("Col" & i & "_D").FormatConditions.Delete
        
        If i = 6 Or i = 12 Then
        '10092543
            Set objFrc = Me.fsbResourceDetails("Col" & i & "_D").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)<" & GetCFWkMin()) '& " AND nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)>0"
            Set objFrc = Me.fsbResourceDetails("Col" & i & "_D").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)>" & GetCFWkMax())
                
            With Me.fsbResourceDetails("Col" & i & "_D").FormatConditions(0)
                .BackColor = 16777164
                .ForeColor = 0
                .FontBold = True
            End With
            With Me.fsbResourceDetails("Col" & i & "_D").FormatConditions(1)
                .BackColor = 255
                .ForeColor = 0
                .FontBold = True
            End With
        Else
            Set objFrc = Me.fsbResourceDetails("Col" & i & "_D").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)<" & GetCFDayMin()) '& " AND nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)>0"
            Set objFrc = Me.fsbResourceDetails("Col" & i & "_D").FormatConditions.Add(acExpression, , "nz([total_" & Me.fsbResourceDetails.Form("Col" & i & "_D").ControlSource & "],0)>" & GetCFDayMax())
                
            With Me.fsbResourceDetails("Col" & i & "_D").FormatConditions(0)
                .BackColor = 16777164
                .ForeColor = 0
                .FontBold = True
            End With
            With Me.fsbResourceDetails("Col" & i & "_D").FormatConditions(1)
                .BackColor = 255
                .ForeColor = 0
                .FontBold = True
            End With
        End If
Next i
End Sub

 

[MichaelRed]

unless the format conditions are to change, there is no need or use in running the procedure. since you have no conditions for the code, there is little reason to be concerned about effiency.




MichaelRed

 

[SkipVought]

"I am not sure how to set the default settings if any of the Conditions are not met?"

It the format of the cells, apart from the CF. Plain vanilla.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[neemi]

thanks for the replys.
I set the format conditions when the form is loaded as the user has the ability via another admin screen to set or change minimum or maximum values, hence setting the CF via code.