Type Mismatch Error (Error 13) in a SELECT/CASE Routine (Nested For Each Loop) for Conditional Formatting

[messadua]

Hello,

I am trying to define a VBA MAcro for Excel 2019. I have a reasonably big dataset in which every column (variable) has numbers with different scales and meanings. Would like to automate conditional formatting
so to loop through each column in the range of interest, identify empty cells as well as values > 95ht percentile and < 5th percentile and highlight all of them.

This is the code I have come up with

Sub PreProcessBeta()

Dim sRow As Long ' Starting Row
Dim sCol As Long ' Starting Column
Dim lRow As Integer ' Last Row of the Analysis
Dim lCol As Integer ' Last Column of the Dataset
Dim rCol As Range ' Current Column under Analysis
Dim rng As Range ' Final Dynamic Range
Dim c As Range
Dim rCol_95 As Variant ' 95th Percentile of rCol
Dim rCol_5 As Variant ' 5th Percentile of rCol


' Hard Code First Row and First column of interest
sRow = 2
sCol = 5

' Define Last Row, Dynamically
lRow = Range("A1048576").End(xlUp).Row
'use the lRow to help find the last column in the range
lCol = Range("XFD" & lRow).End(xlToLeft).Column
Set rng = Range(Cells(sRow, sCol), Cells(lRow, lCol))

'First delete the formatting conditions for the range to start fresh.
rng.FormatConditions.Delete

'Start the First Loop: Select entire active Column and compute Percentiles of Interest, for each active column
For Each rCol In rng.Columns

rCol_95 = Application.WorksheetFunction.Percentile_Inc(rCol, 0.95)
rCol_5 = Application.WorksheetFunction.Percentile_Inc(rCol, 0.05)

For Each c In rCol
' For Each cell in the active Column (rCol is the entire Column under analysis (a Range))

Select Case c.Value

Case Is = "."
c.Interior.ColorIndex = 35 ' light green for missing values
c.Font.Bold = False

Case Is >= rCol_95
c.Interior.ColorIndex = 38 ' light pink for values over the 95th percentile
c.Font.Bold = True

Case Is <= rCol_5
c.Interior.ColorIndex = 37 ' light blue for values over the 25th percentile
c.Font.Bold = True

Case Else
c.Interior.ColorIndex = 2
c.Font.Bold = False

End Select

Next c

Next rCol


End Sub

The dynamic range selection part opf the macro seems working fine but when I run the entire code I get a "Type Mismatch Error (Error 13), apparently related to the content of the c.Value
In fact, any occurrence of the Case Is command produce the same error (either ".", >= rCol_95 or even <= rCol_5).

Any help or advice would be really really appreciated,

Thank you in advance,

Diego


Following you find a minimal version of the dataset for testing purposes

Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	nrec	pid	timewave	clinic	age	bsa	echo_avel	echo_ealat	echo_eamean	echo_earat	echo_easept	echo_edect	echo_eearat	echo_evel
2	1	1	1	VIT0000015822	52.00	1.83	0.20	0.13	0.09	3.70	0.04	173.00	8.71	0.74
3	2	1	2	VIT0000015822	52.00	1.85	0.39	0.10	0.08	1.72	0.06	184.00	8.40	0.67
4	3	1	3	VIT0000015822	52.00	1.83	0.47	0.10	0.09	1.23	0.07	242.00	8.40	0.58
5	4	1	4	VIT0000015822	52.00	1.83	0.51	0.12	0.09	1.12	0.06	200.00	8.40	0.57
6	5	1	5	VIT0000015822	52.00	1.83	0.47	0.10	0.09	1.30	0.07	249.00	7.18	0.61
7	6	2	1	VIT0000017729	76.00	2.01	0.69	0.06	0.06	1.10	0.06	247.00	12.67	0.76
8	7	2	2	VIT0000017729	76.00	2.00	0.78	0.06	0.06	0.96	0.06	242.00	12.50	0.75
9	8	2	3	VIT0000017729	76.00	2.00	0.76	0.03	0.06	0.75	0.09	278.00	9.50	0.57
10	9	2	4	VIT0000017729	76.00	1.98	0.75	0.04	0.04	0.85	0.04	220.00	16.00	0.64
11	10	2	5	VIT0000017729	76.00	2.00	1.00	0.03	0.04	0.56	0.05	282.00	14.00	0.56
12	11	2	6	VIT0000017729	76.00	1.93	0.79	0.04	0.04	0.68	0.04	238.00	13.50	0.54
13	12	3	1	VIT0000036955	79.00	1.53	0.20	0.10	0.07	3.45	0.04	224.00	9.86	0.69
14	13	3	2	VIT0000036955	79.00	1.48	0.34	0.04	0.04	1.59	0.03	253.00	15.43	0.54
15	14	3	3	VIT0000036955	79.00	1.47	.	0.04	0.03	.	0.02	217.00	22.00	0.66
16	15	3	4	VIT0000036955	79.00	1.51	.	0.04	0.03	.	0.02	238.00	22.33	0.67
17	16	4	1	VIT0000028014	61.00	1.76	0.90	0.07	0.07	0.78	0.07	198.00	10.00	0.70
18	17	4	2	VIT0000028014	61.00	1.76	0.58	0.07	0.07	0.86	0.07	262.00	7.69	0.50
19	18	4	3	VIT0000028014	61.00	1.76	0.80	0.06	0.06	0.84	0.05	154.00	12.18	0.67
20	19	4	4	VIT0000028014	61.00	1.79	0.96	0.07	0.06	0.59	0.05	176.00	9.50	0.57
21	20	4	5	VIT0000028014	61.00	1.79	.	.	.	.	.	.	.	.
22	21	4	6	VIT0000028014	61.00	1.79	0.58	0.07	0.06	0.93	0.05	167.00	9.00	0.54
23	22	4	7	VIT0000028014	61.00	1.79	0.79	0.09	0.07	0.80	0.05	226.00	9.00	0.63
24	23	5	1	VIT0000032671	63.00	1.64	0.70	0.10	0.10	0.86	0.10	215.00	6.00	0.60
25	24	5	2	VIT0000032671	63.00	1.64	.	.	.	.	.	.	.	.
26	25	5	3	VIT0000032671	63.00	1.66	0.64	0.09	0.07	0.73	0.05	226.00	6.71	0.47
27	26	5	4	VIT0000032671	63.00	1.67	0.45	0.11	0.08	1.89	0.04	135.00	11.33	0.85
28	27	5	5	VIT0000032671	63.00	1.68	0.55	0.06	0.06	0.78	0.05	250.00	7.82	0.43
29	28	5	6	VIT0000032671	63.00	1.68	0.67	0.10	0.07	0.66	0.04	121.00	6.29	0.44
30	29	5	7	VIT0000032671	63.00	1.66	0.72	0.10	0.07	0.71	0.04	234.00	7.29	0.51
31	30	6	1	VIT0000038852	73.00	1.53	0.63	0.07	0.07	0.79	0.07	227.00	7.14	0.50
32	31	6	2	VIT0000038852	73.00	1.63	0.36	0.07	0.05	1.72	0.05	263.00	12.40	0.62
33	32	6	3	VIT0000038852	73.00	1.62	0.48	0.08	0.08	0.88	0.07	274.00	5.60	0.42
34	33	6	4	VIT0000038852	73.00	1.59	0.36	0.07	0.07	1.00	0.06	122.00	5.54	0.36
35	34	6	5	VIT0000038852	73.00	1.63	0.85	0.10	0.09	0.54	0.07	235.00	5.41	0.46
Sheet1

 

[DanteAmor]

Hi and welcome to MrExcel.

What you have in 'c' in this line: For Each c In rCol is range of the entire column of 'rCol'.
To read a cell you need to iterate through all the cells in the column:

Change this

For Each c In rCol

For this:

For Each c In rCol.Cells

 

[messadua]

Hi and welcome to MrExcel.

What you have in 'c' in this line: For Each c In rCol is range of the entire column of 'rCol'.
To read a cell you need to iterate through all the cells in the column:

Change this

For Each c In rCol

For this:

For Each c In rCol.Cells

Easy, but Effective and Wonderful fix, it is now working like a champ !

Thank you so very much, you made my day and saved me a lot of work

 

[DanteAmor]

Im glad to help you, thanks for the feedback.