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]

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.

What I put first may not have been too clear. I just indicated the amount of characters next to the specific word which is a column header. My last post is a bit clearer I think.

 

[VoG]

Maybe this

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 = 3 To LR
    For j = 3 To LC
        If Len(Cells(i, j - 1).Value) > Cells(2, j).Value Then Cells(i, j - 1).Interior.ColorIndex = 3
    Next j
Next i
End Sub

 

[Dazzawm]

Again I don't understand the macro. Where does it say that it needs to look anywhere in column A and any cell with more than 20 characters it will be coloured red, looks at column F and any cell with more than 4 characters that also coloured red and so on. The numbers in the sample are for info only they will not be on the spreadsheet.

 

[VoG]

The numbers in the sample are for info only they will not be on the spreadsheet.

So where do these numbers come from? I can write VBA but I am not a magician.

 

[Dazzawm]

So where do these numbers come from? I can write VBA but I am not a magician.

I dont know how clearer I can be. The number 20 is the most amount of characters there can be in any cell in column A and so on. So if "Liverpool Football Club" was in cell A26 it would be coloured red as it has over 20 characters (23)

 

[VoG]

Where does the 20 come from?

 

[Dazzawm]

Its what I need for the project I am doing, that number needs to be put in the code for that particular column, because a program we use wont allow any more in that column for the program the spreadsheet is loaded into. that is why the numbers are beneath the particular column heading to tell you the most allowed in that column.

 

[VoG]

OK, this expects the max character count in row 2 of ach column

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 = 3 To LR
    For j = 1 To LC
        If Len(Cells(i, j).Value) > Cells(2, j).Value Then Cells(i, j).Interior.ColorIndex = 3
    Next j
Next i
End Sub

 

[Dazzawm]

I still dont think you grasp what I need. The row of numbers in the sample will not be on any worksheet they are to tell you what amount of characters in each column needs to be written in the code. I dont know something like:-

Range.Column A = <=20
Range.Column B = <=20
Range.Column D = <=7

and so on....

 

[VoG]

Try

Sub daz()
Dim LR As Long, LC As Long
Dim i As Long, j As Long
Dim X
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
X = Split(InputBox("Enter " & LC & " max characters separated by commas"), ",")
If UBound(X) <> LC - 1 Then
    MsgBox "Wrong number of data input"
    Exit Sub
End If
For i = 2 To LR
    For j = 1 To LC
        If Len(Cells(i, j).Value) > Val(X(j - 1)) Then Cells(i, j).Interior.ColorIndex = 3
    Next j
Next i
End Sub