excel macro help. autoformat?

Question

i need a macro to do the following,

look through column c and if the cell has 4 charectors whether numbers or letters or fewer, change the color of the cell or change it in someway to perform the next function add specfic text to the cell next to it example xxxx in the cell next to it. then continue to the next cell below untill cell is empty. so that i can sort it later so that only those cells are at the top. or delete all rows containg that xxxx

Answer 1

You don't need a macro to do that. I'm good at writing macros, but this is not a situation where you need one.

Use something like this:

=IF(LEN(A1) <=4, TRUE, FALSE)

In this example you would probably put the formula in the next row. For this example, I'd put the formula in cell B1 and copy it down the B column. This formula evaluates the number of characters in cell A1. If the number of characters is less than or equal to 4, it will return a value of TRUE in B1, if A1 has more than for characters it will return FALSE as the value for B1. If you want to return values for B1 that are different than TRUE or FALSE, just put the text in quotes like this:

=IF(LEN(A1) <=4, "4 or less", "more than 4")

Then you can sort or filter your data based on the values in Column B.

You could also use Conditional Formating and specify a formula to color the cell. But it would be much easier to sort or delete if you have values to sort by in an adjoing column such as in my example instead of trying to sort or delete manually based on which cells are colored.

I only recommend using a macro unless you are wanting to simplify things for a user other than yourself who doesn't know much about Excel and where you'd want them to be able to just click a button and have it done.

If it is only going to be used once or twice it will take much longer to write the macro and use it than it will to write the Excel function and that macro won't automatically update like the Excel function will if cell A1 gets changed from being 7 characters long for some reason to 3, UNLESS you specifically make sure to set up your macro code to do so.

Answer 2

counting on your version of workplace, because of the fact the previous poster noted, having macros run while a workbook opens could be problematical. by ability of default Excel does no longer enable macros to run with out the person particularly allowing them. you could enable that habit by ability of enhancing macro protection and allowing all macros to run, yet that it fairly no longer cautioned. you could desire to objective putting a message on the 1st worksheet and then enhancing that message as quickly as macro are enabled by ability of the person, yet that could require placing the message returned on the workbook close journey.

Answer 3

Hi,
In a VBA module type the following subroutine:
-------------------------------------------------------------------------------
Public Sub s()
Dim i As Integer
i = 1
While Not IsEmpty(Sheet1.Cells(i, "C"))
If Len(Sheet1.Cells(i, "C")) <= 4 Then
Sheet1.Cells(i, "C").Font.Color = vbRed
Sheet1.Cells(i, "D") = "xxxx"
End If
i = i + 1

Wend
End Sub
----------------------------------------------------------------------