Creating highlighting rules based on string values

[VBA_PM]

Hey all,

I'm doing some data analysis work on a bunch of spreadsheets and I'm seeking a way to automate part of the process.

The entire row is highlighted based on the string value in column I, there's many different string values I'll need to create rules for. The longest string I've counted is 59 characters. I've attempted different things but haven't had any luck creating functioning code for it. My knowledge with VBA is pretty minimal. Any guidance is greatly appreciated.

 

[6StringJazzer]

I suspect this can be done without VBA, and just conditional formatting. But I would need some details. What are the "many different string values"? Do you have a list someplace?

As an example suppose you have a list of the string values in Sheet2 column A. Then you would select all the columns you want to highlight and add this conditional formatting rule, using a formula:

=ISNUMBER(MATCH($I1,Sheet2!$A:$A,0))

You mention a bunch of spreadsheets. I don't think CF rules can have references to external files. The way I would do this is to have one file with the list, and then in each file you want to analyze add a sheet that has external references to that file, and then use the above technique.

Of course, you could do this with VBA, but we need more detail about how you would want that to work in more than one file. If the above will not get you want you want, please explain what else you need that the above does not provide, details of the list of strings, what columns are included in the "the entire row".

 

[VBA_PM]

Thanks for the reply! I do not have a list at the moment, I started on one but I haven't finished it. I can however upload an example of one of my spreadsheets. This is just an image example for now, but I can upload an XL2BB example tonight if that would be helpful.

It is a good many spreadsheets.. I process about 200 of these spreadsheets at a time. But, I've been manually highlighting them for a long time now. So doing them individually isn't a big deal I just want to be a bit more efficient if possible. The highlighting portion is what takes the vast majority of time with these. Especially in some of the larger examples where there's over 20k rows.

As a quick breakdown the way these files are processed now goes:

Downloading the files one at a time from web service as CSV files > Use a VBA macro to convert to xls and save as a unique file > repeat for all files > Then I open each of them individually highlight the rows that contain certain values > Resave > Distribute

Certain things are highlighted red if they're a high priority error (contain an E in column F), and the other issues are highlighted in alternating yellow/orange. The alternating is optional, this is just done to make it a bit easier to distinguish between them.

 

[VBA_PM]

I should note, the reason I mentioned certain string values is because only some of the issues are highlighted.

There are cases where there will be data, but no highlighting should occur. It will all be dependent on what value is in column I.

 

[VBA_PM]

70420 noMRN.xlsx
	A	B	C	D	E	F	G	H	I	J	K
14	Provider	Representative Facility ID	User	Profile	Import Log ID	Error / Warn	Date Sent		Issue	Issue Location	Message Control ID
15	12345		Testname.HL7	1	28622837	E	7/6/2022 5:42		patient id is missing	PID-3
16	12345		Testname.HL7	1	28622838	E	7/6/2022 5:42		patient id is missing	PID-3
17	12345		Testname.HL7	1	28622891	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
18	12345		Testname.HL7	1	28622892	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
19	12345		Testname.HL7	1	28622893	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
20	12345		Testname.HL7	1	28622894	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
21	12345		Testname.HL7	1	28622895	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
22	12345		Testname.HL7	1	28622906	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
23	12345		Testname.HL7	1	28622907	E	7/6/2022 5:43		Patient id was not found, must be of type 'MR'
24	12345	SIISCLIENT11	Testname.HL7	1	28802461	W	7/14/2022 11:30		next of kin name first is invalid	NK1-2.2
25	12345	SIISCLIENT11	Testname.HL7	1	28802701	W	7/14/2022 11:34		next of kin name first is invalid	NK1-2.2
26	12345	SIISCLIENT11	Testname.HL7	1	28824492	W	7/15/2022 10:10		next of kin name first is invalid	NK1-2.2
27	12345	SIISCLIENT11	Testname.HL7	1	28824494	W	7/15/2022 10:10		next of kin name first is invalid	NK1-2.2
28	12345	SIISCLIENT11	Testname.HL7	1	28825442	W	7/15/2022 10:29		next of kin name first is invalid	NK1-2.2
29	12345	SIISCLIENT11	Testname.HL7	1	28786419	W	7/13/2022 16:14		next of kin name first is missing	NK1-2.2
30	12345	SIISCLIENT11	Testname.HL7	1	28766451	W	7/13/2022 9:06		next of kin name last is invalid	NK1-2.1
31	12345	SIISCLIENT11	Testname.HL7	1	28786417	W	7/13/2022 16:14		next of kin name last is invalid	NK1-2.1
32	12345	SIISCLIENT11	Testname.HL7	1	28766215	W	7/13/2022 9:01		next of kin name last is missing	NK1-2.1
33	12345	SIISCLIENT11	Testname.HL7	1	28786419	W	7/13/2022 16:14		next of kin name last is missing	NK1-2.1
34	12345	SIISCLIENT11	Testname.HL7	1	28722256	W	7/11/2022 13:03		next of kin phone is invalid	NK1-5
35	12345	SIISCLIENT11	Testname.HL7	1	28724740	W	7/11/2022 13:52		next of kin phone is invalid	NK1-5
36	12345	SIISCLIENT09	Testname.HL7	1	28861332	W	7/18/2022 11:10		next of kin phone is invalid	NK1-5
37	12345	SIISCLIENT11	Testname.HL7	1	28861550	W	7/18/2022 11:15		next of kin phone is invalid	NK1-5
38	12345	SIISCLIENT09	Testname.HL7	1	28694329	W	7/8/2022 16:45		patient first name is invalid	PID-5.2
39	12345	SIISCLIENT11	Testname.HL7	1	28567455	W	7/1/2022 8:26		patient mothers maiden name is missing	PID-6
Provider_Detail_Error_Report (3