vendredi 17 avril 2015

Add comment to Cells with Specific colors

I am trying to create a macro that will search a column of text (A:A) for a specific interior color. In this case the interior color is 55. Normally I'd create a range of A1:A101 but the data that is added changes daily so there may be more or less.


Essentially once the macro identifies the cells with the colors I want the macro to add a comment to the cell. Something simple like "Hello World!".


So far this is what I have:



Sub AddCommentBasedOnColor()
Dim rng As Range, cell As Range

Set rng = Range("G:G")

Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each cell In rng
If cell.Interior.ColorIndex = 55 Then
If rng.Comment Is Nothing Then rng.AddComment
rng.Comment.Text "Possible Aux Stacking"
End
End If
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub


The problem that I am running into is that when I am running the code, the comment portion does not work at all. No comments are made and for some reason I get a debug code but did not have one before. Not sure what I did that changed it.


Additionally, when I remove the commenting section of this code it does take some time to run, any assistance with shortening that length of time would be appreciated as well.


Aucun commentaire:

Enregistrer un commentaire