Automatic Fill Colour to The Cells

[cyliyu]

Hi,
Need some help or advise, is this possible.
How can I make a range of cell auto fill up the colour when a comment is added by a user?

 

[Logit]

Note that there is something going on with your posted workbook. I had to use a clean workbook for the Conditional Formatting to function as desired.

 

[cyliyu]

The formula only fills the cell if it is not empty but not comments.

 

[Logit]

Option Explicit

Sub IntColorComments()
Dim Rng As Range
Dim WorkRng As Range

On Error Resume Next

Set WorkRng = Application.Selection
Set WorkRng = ActiveCell
Set WorkRng = WorkRng.SpecialCells(xlCellTypeComments)

Application.ScreenUpdating = False

For Each Rng In WorkRng
    Rng.Interior.Color = vbGreen
Next

Application.ScreenUpdating = True

End Sub

 

[cyliyu]

Thanks, Logit.

It seems like it can only be done with VBA Code.
I did not explain well when started the topic.
I have certain Columns the colour (light blue) was fixed and cannot be changed.
A conditional formatting was set to change the colour to green when a text was input.
What I want was to change the colour to "red" when additional comments were added to the cell.

 

[Logit]

Change the existing lines to this :

For Each Rng In WorkRng
    Rng.Interior.ColorIndex = 40
Next

The macro will only affect those cells where a COMMENT has been added. It will not affect cells that only have data entered.

 

[cyliyu]

Thanks. Logit.
The command line will works if no conditional formatting was applied.
I will remove the conditional formatting setting.

 

[Logit]

Great !

 

[Peter Bartholomew]

It is also possible to combine the code approach with conditional formatting.

Sub SetCommentRange()
Dim WorkRng As Range
Set WorkRng = [Data]
Set WorkRng = WorkRng.SpecialCells(xlCellTypeComments)
WorkRng.Name = "CommentRange"
End Sub

would name a multi-area range consisting of cells with comments.

A defined name 'hasComment?'
= ISREF( thisCell CommentRange )
will establish whether the current cell does or does not have a formula.
Note: 'thisCell' is a relative reference to the current cell (=RC in R1C1 notation).

The conditional format acts along with the 'No Blanks' rule according to the normal ordering rules of conditional formatting.