VBA Don't sum strikethrough not working with conditional format

[NikoDDS]

The VBA code works, when I directly strikethrough a cell which has a value in currency entered with NO conditional formatting.
If I enter a conditional format in column C to strikethrough both cells in column C & D and the column D has a formula based on numeric number entered in column A, the sum of column D still adds the value in strikethrough cells

 

[Rick Rothstein]

The sum of Column D is done through the VBA code? Is it a macro or a UDF (user defined function)?

 

[NikoDDS]

Its a macro in separate Module 1

Public Function ExcStrike(pWorkRng As Range) As Long
'Update 20140819
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
    If Not pRng.Font.Strikethrough Then
        xOut = xOut + pRng.Value
    End If
Next
ExcStrike = xOut
End Function

with =excstrike(D6:D10)" in D11

 

[Fluff]

How about using a helper function like

Public Function ExcStrike(pWorkRng As Range) As Long
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
   If Not Evaluate("IsStrike(" & pRng.Address & ")") Then
        xOut = xOut + pRng.Value
    End If
Next
ExcStrike = xOut
End Function
Private Function IsStrike(Cl As Range) As Boolean
   IsStrike = Cl.DisplayFormat.Font.Strikethrough
End Function

 

[NikoDDS]

How about using a helper function like

Public Function ExcStrike(pWorkRng As Range) As Long
Application.Volatile
Dim pRng As Range
Dim xOut As Long
xOut = 0
For Each pRng In pWorkRng
   If Not Evaluate("IsStrike(" & pRng.Address & ")") Then
        xOut = xOut + pRng.Value
    End If
Next
ExcStrike = xOut
End Function
Private Function IsStrike(Cl As Range) As Boolean
   IsStrike = Cl.DisplayFormat.Font.Strikethrough
End Function

Yes the helper function helped, Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[NikoDDS]

Using the same function in multiple (3) sheets in a workbook, the don't sum strikethrough works in the first sheet, then as the strikethrough is done in the 2nd sheet and 3rd sheet, the function does not work in 1st sheet. When I redo the function in the 1st sheet, it does not work in the 2nd and 3rd sheet
Not sure how to upload the whole workbook

 

[Fluff]

How about

   If Not pRng.Worksheet.Evaluate("IsStrike(" & pRng.Address & ")") Then

 

[NikoDDS]

Yes that seems to work, all worksheets are calculating correctly, Thanks

 

[Fluff]

You're welcome & thanks for the feedback.