Macro to conditonally format cells >255 characters

[Milos]

Hi all,

Does anybody know how I would code a macro to conditionally format any cell that is >255 characters? The conditional format could be anything but I think changing the cell colour/font colour to red would be the best option.

Here is a simple example of what I am after:

Apple (+255 more characters)	Banana	Cherry
Asparagus	Bean (+255 more characters)	Carrot
Agua	Beer	Coke (+255 more characters

<tbody>
</tbody>

Run macro:

Apple (+255 more characters)	Banana	Cherry
Asparagus	Bean (+255 more characters)	Carrot
Agua	Beer	Coke (+255 more characters

<tbody>
</tbody>

 

[Eric W]

Are you specifically looking for a macro, or will Conditional Formatting do? To use CF, try this:

1) Select the range you want to evaluate (for this example, A1 is the top left corner)
2) Click Conditional Formatting > New Rule > Use a formula > and enter:
=LEN(A1)>255
in the formula box.
3) Click Format... and select a fill color or font color.
4) Click OK.

Let me know if that works for you.

 

[Milos]

Thanks Eric,

Yeah I was specifically looking for a macro unless you know of a better solution to my problem (explained in the next sentence). I have been copy/pasting information from one sheet into another and the condtional formatting seems to get overridden when I paste the information from one sheet to the next. I have tried protecting the sheet from formatting too with no success. I am probobly doing something wrong.

Ultimately this is what I am after:

• Copy cells from Sheet1/Spreadsheet1 (no conditional formatting)

• Paste cells from Sheet1 into Sheet2/Spreadsheet2 (empty sheet with conditional formatting)

• Sheet2 condtional formatting does not get overridden

 

[Peter_SSs]

Are you doing the Copy/Paste manually or by macro?
In either case, instead of Paste, can you use the following?
Paste Special -> Values

 

[Milos]

Thanks Peter,

Problem solved. I had tried that originally but the cells do not automatically format in with colour until I changed out of the Sheet and then back into it for some reason? That is what messed me up.