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>

 

[JackDanIce]

Instead of a macro, why not just use conditional formatting?
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Test</TD><TD style="BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>
Conditional formatting formula in cell F9 is

=IF(LEN($E$9)>$F$9,TRUE,FALSE)

Compare to:
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>

 

[Dazzawm]

As I said too much data and many spreadsheets so a macro would be a great timesaver.

 

[JackDanIce]

I don't really understand why you can't adjust the formula and then copy and paste it into the cells (I think there's a function that allows you to copy conditional formatting to similar cells), probably be as quick and without the need to run a macro, but anyway, what is your data ranges and name of your spreadsheets?

 

[Dazzawm]

The spreadsheets will be called Sheet1 but the ranges will differ in each one and the headings may vary which column they are in, which is why I gave the heading name.

 

[Dazzawm]

^

 

[VoG]

Maybe this (with the sheet selected)

Sub daz()
Dim LR As Long, LC As Long
Dim i As Long, j As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = 1 To LR
    For j = 2 To LC Step 3
        If Len(Cells(i, j - 1).Value) > Cells(i, j).Value Then Cells(i, j - 1).Interior.ColorIndex = 3
    Next j
Next i
End Sub

 

[Dazzawm]

How will this work then VoG. I cant see any indication of the character length to the specific column headings?

 

[Dazzawm]

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"><COL style="WIDTH: 116px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><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><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">SubModel</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">EngineCode</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Series Identifier</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">BodyType</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">FuelType</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Transmission</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">WheelBase</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">Camshaft</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">BHP</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">EngineNo.</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold; TEXT-DECORATION: underline">ChassisNo.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">20</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">20</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">20</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">60</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial; FONT-SIZE: 10pt">60</TD></TR></TBODY></TABLE>

The numbers are most amount of characters allowed in that specific column beneath that heading so I need them coloured or a pop up to tell me the cell that is over the amount. As I said using Len is a long way of going about it as there are about 10000 rows and I will have to do the same on about 40 spreadsheets.

 

[VoG]

I don't see any column headings. It checks every third column.

Edit. That is completely different to what you posted before! Please confirm what you have and what you want to happen.