vba IF Accounting format Then "0"

[gtd526]

Hello,
Im using the following code to remove text if a desired value is found (N:N="sold"). It will delete the values for that particular row.
E:E and F:F are accounting formats and I would rather insert "0" to those cells rather than delete the value. Thank you.
Here is the code and the worksheet:

Sub ClearSold()
'ClearContents w/o removing formula

    Dim Rng As Range
    Dim Cel As Range
    Dim Ws As Worksheet
   
        Set Rng = Range("N9:N26")
        For Each cell In Rng
            'select the cells First
            If cell.Value = "sold" Then
                cell.Select
                ActiveCell.Offset(0, -13).Select
                Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 14)).Select '.ClearContents
            'how to confirm 'accounting format'?
            '.NumberFormat = "_($*#,##0.00_);_($*(#,##0.00);_($*""_""??_);_(@_)"
            'change E:E, F:F to zero, dont delete
'''''''''''''''''''''''''''''''''''''''''''''''''
            For Each Cel In Selection
            If Cel.NumberFormat = "$*#,##0.00" Then  'not recognizing account format
                Cel.Value = 0
            End If
'''''''''''''''''''''''''''''''''''''''''''''''''
            'clearcontents if not a formula
            'change to "0" if contains "="
            If InStr(Cel.Formula, "=") = 0 And Cel.Value <> "" Then
                Cel.ClearContents
            End If
            Next Cel
            End If
        Next
       
End Sub

Worksheet:

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
7				ETNA				ETNA			AVG	AVG		sold
8	Symbol	Sector	Type	Shares	Cost	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %	Weight(%)	Expiration	Strike	Expires in:
9	AMZN	Retail	call	9	$ 5.05	$ 6.50	$ 6.50	$ 5.05	$ 4,545.00	$ 5,850.00	$ 1,305.00	28.71%	23.3%	6/18	$3,100	28	day(s)
10	MSFT	Tech Srvc	call	10	$ 15.00	$ 17.00	$ 17.00	$ 15.00	$ 15,000.00	$ 17,000.00	$ 2,000.00	13.33%	76.7%	sold	$30
11					$ -	$ -	$ -	$ -	$ -	$ -	$ -
12					$ -	$ -	$ -	$ -	$ -	$ -	$ -
13					$ -	$ -	$ -	$ -	$ -	$ -	$ -
14					$ -	$ -	$ -	$ -	$ -	$ -	$ -
15					$ -	$ -	$ -	$ -	$ -	$ -	$ -
16					$ -	$ -	$ -	$ -	$ -	$ -	$ -
17					$ -	$ -	$ -	$ -	$ -	$ -	$ -
18					$ -	$ -	$ -	$ -	$ -	$ -	$ -
19					$ -	$ -	$ -	$ -	$ -	$ -	$ -
20					$ -	$ -	$ -	$ -	$ -	$ -	$ -
21					$ -	$ -	$ -	$ -	$ -	$ -	$ -
22					$ -	$ -	$ -	$ -	$ -	$ -	$ -
23					$ -	$ -	$ -	$ -	$ -	$ -	$ -
24					$ -	$ -	$ -	$ -	$ -	$ -	$ -
25					$ -	$ -	$ -	$ -	$ -	$ -	$ -
26					$ -	$ -	$ -	$ -	$ -	$ -	$ -
27									$ 19,545.00	$ 22,850.00	$ 3,305.00	16.91%
ETNA

Cell Formulas
Range	Formula
G9:G26	G9	=IF($F9=0,0,MAX($F9,$G9))
H9:H26	H9	=IF($H9=0,$F9,MEDIAN($H9,$F9,0))
I9:I26	I9	=(E9*D9)*100
J9:J26	J9	=(F9*D9)*100
K9:K26	K9	=J9-I9
L9:L26	L9	=IF(K9<>0,K9/I9,"")
M9:M26	M9	=IF(I9<>0,I9/$I$27,"")
P9:P26	P9	=IF(OR($N9="",$N9="sold"),"",$N9-TODAY())
Q9:Q26	Q9	=IF(OR($N9="",$N9="sold"),"","day(s)")
O22:O26,O19:O20,O10:O12	O10	=IF($D10>0,$I10/(5*100),"")
B24:B26,B9:B21	B9	=IF(COUNTIF(Sectors!$A$3:$V$50,A9)=1,INDEX(Sectors!$A$2:$V$2,MAX((Sectors!$A$3:$V$50=A9)*(COLUMN(Sectors!$A$2:$V$2)))),"")
I27:K27	I27	=SUM(I9:I26)
L27	L27	=IFERROR((K27/I27),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[johnnyL]

@gtd526 Try replacing the line:

If Cel.NumberFormat = "$*#,##0.00" Then  'not recognizing account format

with the following line:

If Mid(Range(Cel).NumberFormat, 1, 15) = "_($* #,##0.00_)" Then

 

[gtd526]

@gtd526 Try replacing the line:

If Cel.NumberFormat = "$*#,##0.00" Then  'not recognizing account format

with the following line:

If Mid(Range(Cel).NumberFormat, 1, 15) = "_($* #,##0.00_)" Then

I entered your line but received the following error:
Method 'Range' of object '_Global' failed

 

[johnnyL]

How about this as a replacement:

If Mid(Cel.NumberFormat, 1, 15) = "_($* #,##0.00_)" Then

 

[gtd526]

How about this as a replacement:

If Mid(Cel.NumberFormat, 1, 15) = "_($* #,##0.00_)" Then

Works. Thank you.

 

[johnnyL]

Glad to hear it!