Add cell comments in XL where a condition applies

[sinistapenguin]

Hi All

I have been trying to write a macro to run through a column of an excel sheet and basically validate the data.

At the moment it runs through and changes the cell formatting based on 1 condition.

eg. Where the data is longer than 10 characters it colours the cell background Red and sets the Text to White.

What I would really like it to do is in addition to this formatting, I would like it to add a cell comment which states what the problem is.

eg.
It would check the length, then add a comment that says "Too Many Characters"
It would then check whether is was text in a numeric field and either add a comment (if one doesn't exist), or update a comment with "Alpha Characters Detected" if alpha characters were found.

The macro I have written so far is:

Sub Buildings()

' Validate Building Code
Columns("A:A").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(LEN(A1)>10)"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub

I know that I can use the AddComment function, but I can't figure out how to make it add the comment to the current cell rather than a specific one.

Anyone able to help?

 

[SkipVought]

Hi,

Have you looked at the Data/Validation feature in Excel? This catches the problem before the horse is out of the barn.

Skip,
[sub]
[red][/red]
[/sub]