Macro To Tell Me There Are Too Many Characters

[Dazzawm]

Hi All. In the table below is header titles with a number next to them. What I need the macro to do is look for the column header title and either a pop up occurs or the cell is coloured in when there are more than the number.

i.e the column that has the title SubModel should have no more than 20 characters within any cell in that column etc..

There are 100,000s of cells so Len, conditional formatting etc would be tricky and I would want to use the macro on loads of spreadsheets.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 109px"><COL style="WIDTH: 21px"><COL style="WIDTH: 21px"><COL style="WIDTH: 90px"><COL style="WIDTH: 14px"><COL style="WIDTH: 23px"><COL style="WIDTH: 79px"><COL style="WIDTH: 14px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">SubModel</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">BodyType</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">WheelBase</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Series Identifier</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Transmission</TD><TD style="TEXT-ALIGN: center">4</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Camshaft</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">EngineCode</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">FuelType</TD><TD style="TEXT-ALIGN: center">7</TD><TD></TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">BHP</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">EngineNo.</TD><TD style="TEXT-ALIGN: center">60</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold; TEXT-DECORATION: underline">ChassisNo.</TD><TD style="TEXT-ALIGN: center">60</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

 

[Dazzawm]

How does this work then? I run the macro and a box comes up saying 'Enter 36 max characters seperated by commas'

 

[VoG]

You enter the max no of characters for each column reading from left to right like

10,2,3

 

[Dazzawm]

Done that and a box appears saying 'wrong number of data input'?

 

[VoG]

If you have 36 columns you have to enter 36 numbers, separated by commas.

 

[Dazzawm]

Ok so I have to adjust the code to the number of columns I have?

 

[VoG]

No, the code calculates the number of columns (LC). You just have to tell it the maximum length of text for each of those columns when prompted.