Conditional formatting for cell lengths - VBA

[Gragra01]

Hi there,
There seems to be a some similar psots to what i need, but not exactly what i want.

What i need is to use conditional formatting, to change the formatting of a cell, based on whether the cell contents is greater than x characters in length (this changes from columns to column

Conditional formatting works ok, but what i would like to do is have some code, search for the column heading,
which is always the same, but the position changes for every file, and then in that column, apply the above
formatting if the cell length is too long.
Can this be done? Have I confused everyone?

The files that i am using, are actually data input files for another system, so for example, Colum A has some data, but the system that it is written into, may have a limitation of 10 characters for that particular field.

What i want to do is to flag all the rows that have cells that are greater than 10 characters in length for that column, and then Column B may have a limitation of 15 characters, and so forth.

Ideally i would then like to cut them to a new worksheet, but one problem at a time.

Any help would be great.

Thanks
Gragra

 

[littlewoman]

I'm only in the early stages of vba
but I would tackle it like so:

Dim iColumn as integer
Dim iRow as integer

'search heading
iColumn = 0
While worksheets("[name]").cells(1,1 + iColumn).Value <> [columnheading]
iColumn = iColumn + 1
Wend

'check length cellcontent and apply formatting
for iRow = 2 to 65536
if len(worksheets("[name]").cells(iRow, iColumn.value<=10 then
worksheets("[name]").cells(iRow, iColumn).forecolor = &H0& 'black
else
worksheets("[name]").cells(iRow, iColumn).forecolor = &HFF& 'red
end if
next iRow

maybe there is even a better way but as I said I'm only in the early stages. Yet I hope that maybe I contributed something.
if not, no hard feelings, just wait for the next one to come along ;-)

 

[SkipVought]

Hi,

You do NOT need VB Code to do this.

Use the native Excel Conditional Formatting available in the worksheet.

Test the Length. For instance, if your data begins in row 2 and you are testing column A...
[tt]
=Len($a2)>10
[/tt]
It's that simple!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Gragra01]

Hi Skip,

Thanks for the reply,

I am currently using the conditional formatting, but with 15+ columns, and 2-3 files a week, i am trying to automate the process by using code.

If you have any suggestions, i would lve to hear them!

Gragra

 

[PHV]

Have you tried to play with the macrorecorder when you manually set the conditional format ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gragra01]

Hi PHV,

Thanks for getting back to me.

My thinking is that as the columns are always in a diffrent position that recording the macro would be "absolute" so would format the same columns all the time, regardless of the column name?

So that is why i am trying to do this through code. Litlle women has given me a start, I'm trying to use that as a base, but any extra help would be great.

Thanks
Gragra

 

[PHV]

What I suggested is that you examine the code generated by the macrorecorder and then tweak it with the variable column stuff you already should have.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Gragra01]

Ahhh,

Sory for the misunderstanding, I'll be looking at that!

Thanks
Gragra

 

[SkipVought]

Of COURSE, your recorded macro is absolute. It's a staring point!

Macro record setting up a conditional format on one sheet.

Modify the make it general.

Create a loop to handle the multiple sheets.

VOLA!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]