to this

to HTML tables like this

Kit	Name	3	3	1	3	3	3	4	3	4	3	3	3	4	4	4	4	4	4	4	4	4	4	4	4	4
		9	9	9	9	8	8	2	8	3	8	9	8	5	5	5	5	5	4	3	4	4	6	6	6	6
		3	0		1	5	5	6	8	9	9	2	9	8	9	9	5	4	7	7	8	9	4	4	4	4
						a	b				.		.		a	b							a	b	c	d
											1		2
Kit1	Name1	13	25	14	11	11	13	12	12	12	14	14	29	17	9	15	11	11	25	15	18	30	15	16	17	17
Kit2	Name2	13	25	14	11	11	13	12	12	12	13	14	29	17	9	14	11	11	25	15	18	30	15	15	17	17
Kit3	Name3	12	25	14	11	11	13	12	12	12	13	14	26	17	9	14	11	11	25	15	18	30	15	15	17	17
Kit4	Name4	13	25	14	11	11	13	13	12	12	13	14	29	17	9	15	11	11	25	15	18	30	15	16	17	18
Kit5	Name5	13	27	14	11	12	13	12	12	12	15	14	29	17	9	15	11	11	25	15	18	30	15	16	17	19
Kit6	Name6	13	25	14	11	11	13	12	12	12	13	14	29	17	9	16	11	11	25	15	18	30	15	14	17	17
Kit7	Name7	13	25	14	11	11	13	12	12	12	13	14	30	17	9	16	11	11	25	15	18	30	15	16	16	17

with just a few mouse clicks.

Excel Macros

• Highlight selected cell differences macros will compare each cell in a column of the selected region with a reference value. If the cell value is the same as the reference value, the background is set to white. If there is a difference in values then the cell background will be colored: pale yellow for a difference of one, pale green for a difference of two, and pale red for differences of three or more. Three different macros are provided that use different choices for the reference value.
  • SetBGColorAnyRowReference requires you to input the row number of the reference values. The cell in the reference row corresponding to the column of each cell in the selected region is used to determine the reference value. The reference row may or may not be in the selected region.
  • SetBGColorFirstRowReference uses the first row of the selected region for the reference values.
  • SetBGColorMedianRowReference determines the median value of each column in the selected region and uses this as the reference value.
• Reset selected background macro will reset the background color of the selected region.
  • ResetBGColor changes the background color of the selected region to white. Note that this may change the color of the original background if it was not white.
• Create HTML Table creates a relatively small HTML table based on the selected region. It determines the background color of each cell. If the color is not white, then the corresponding cell in the output HTML table will have the same color as the cell in the Excel table. The table created is a valid HTML file, ready for further custom editing.
  • MakeHtmlTable creates the table and copies it to the clipboard.
  • WriteHtmlTable creates the table and copies it to the clipboard, and also creates or overwrites the file C:\DNA_Table.html.

Download Excel Macros

Normally, I do not like to execute Microsoft Office files with macros because of the potential for viruses. I have included the source files in the zip file so you can import them into your own Excel worksheets or review before enabling macros.

There are two files that contain code for the macros,
1) DNA_Macros.bas, you can view this as a text file: DNA_Macros.txt, and
2) frmDNA_Row.frm, you can view this as a text file: frmDNA_Row.txt.

The computers that I use all have Microsoft Visual Basic installed and I do not know if there will be a problem running these macros on systems without it, but I believe that it should work fine on most systems. The macros work on my computers with Microsoft Office XP running on XP and Office 2000 running on Windows 98.

If you add the macros yourself, you may need to include the Microsoft Forms 2.0 Object Library which is automatically added when you include a button or form.

Step by Step Instructions