Type mismatch error when clearing data

[rodweston]

When I select two or more rows of data, right-click and select 'Clear Contents' I get a type mismatch error in a macro on the following line:
If Intersect(Target, Range("B5:B200")).Value = "x" Then

How would I prevent this from occurring

 

[Legacy 143009]

It triggers Worksheet_Change event again as soon as you write "x" value in the cells.

Try to use like:

  Application.EnableEvents = False
  Intersect(Target, Range("B5:B200")).Value = "x"
  Application.EnableEvents = True

This will allow you to mute events before writing. Then it allows events again.

 

[Peter_SSs]

.. as soon as you write "x" value in the cells.

The OP's code is not writing "x" in any cells.

@rodweston
When you select 2 or more rows (lets use the 3 rows 10:12 as an example), the intersection of the selection and B5:B200 is B10:12
B10:B12 does not have a value, it has 3 values which cannot together be compared to "x", hence the error.

Can you confirm that this is part of a Worksheet_Change event code and, if so, tell us more about exactly what you are trying to if a cell in column B is changed to "x"?

Also, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Legacy 143009]

The OP's code is not writing "x" in any cells.

Oh, I missed If...Then Haven't my second morning coffee yet

 

[rodweston]

I have updated the profile.
If an 'x' is entered in column B, the code calculates and writes data in several other columns in the row.

Emulsion Production Schedule with Raw Materials - Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1								A,B,C,D,E,F,G,H,I,J			3440	68.78/12.12	20.63	22.68	28.32	5.68	808	1620	12.12
2
3	+	H JA004	F JR001	S JR002 Part 1	S JR002 Part 2	Tank Group Used to Homogenize	Done Polymerizing date	Tank Groups Available	JR001 Done Gestating Date	Order/Receive	D4	DBSA	TEOS	Sodium Hydroxide	DEA	1171A SYL-OFF (TIN)	Nalco 1130 Colloidal Silica	Nalco 1115 Colloidal Silica	Tergitol 15-S-9	OH Fluid	15% HCl	Catalyst	AMP-95	Andisil 187	Andisil 137	TK01	JA018	KBM 802	KBM 803	ethyl-hexylacrylate	Tinuvin 400
4								C,D,E,F,G,H,I,J			34,788.00	2,696.00	740.40	1,249.00	365.00	483.80	14,483.30	26,816.20	183.90	38,010.00	5,488.00	274.00	245.00	475	376.83	63.89	1700.7	441	398.7	359.58	0
5
6
7
8
9
10
11
12
13
current

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N5:N86	Cell Value	<150	text	NO
O4:O8,P5:P8,O9:P86,S6:S86	Cell Value	<44	text	NO
M5:M86	Cell Value	<200	text	NO
S4:S86	Cell Value	<73	text	NO
Q5:Q86	Cell Value	<6600	text	NO
L4:L86	Cell Value	<500	text	NO
R5:R86	Cell Value	<10500	text	NO
P4:W4,S5:W5	Cell Value	<44	text	NO

 

[Peter_SSs]

Can you confirm that this is part of a Worksheet_Change event code

You didn't address that point but I'm assuming that case.
I suggest that you try code with this structure

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("B5:B200"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Value = "x" Then
        'Put the code here that calculates and writes data in several other columns in the row.
        
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub