Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
Hi all Can someone advise how to using conditional formatting on cells containing a Comment? I'd be hoping to have some method of identifying these cells and then applying a colour or border format to make it more visible and obvious that they contain a comment. I'm finding the small red triangle a little hard to spot sometimes. Grateful for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
On Sat, 30 Apr 2011 00:09:32 +0100, Colin Hayes wrote:
Hi all Can someone advise how to using conditional formatting on cells containing a Comment? I'd be hoping to have some method of identifying these cells and then applying a colour or border format to make it more visible and obvious that they contain a comment. I'm finding the small red triangle a little hard to spot sometimes. Grateful for any help. I don't believe there is any way of using Conditional Formatting to mark cells containing a comment. You could do it with a VBA Macro. Something like: ==================== On Error Resume Next With Cells.SpecialCells(xlCellTypeComments) .Interior.Color = vbRed .Font.Bold = True End With ================== Of course, you would have to ensure that you didn't change any desired formatting. The above code snippet should at least get you started. But how best to implement it might depend on the kind of worksheet you are designing. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
hi,
there's no formula to verify that cell contain a comment, but you can create one, and use it in conditional formatting =NOT(ISERROR(HasComment(A1))) Function HasComment(rng As Range) As Boolean If Not IsError(rng.Comment.Text) Then HasComment = True End Function or in another way, just execute this macro Sub MyCommentsMoreVisible() With Cells.SpecialCells(xlCellTypeComments) .Interior.ColorIndex = 33 End With End Sub -- isabelle Le 2011-04-29 19:09, Colin Hayes a écrit : Can someone advise how to using conditional formatting on cells containing a Comment? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
In article , isabelle writes
hi, there's no formula to verify that cell contain a comment, but you can create one, and use it in conditional formatting =NOT(ISERROR(HasComment(A1))) Function HasComment(rng As Range) As Boolean If Not IsError(rng.Comment.Text) Then HasComment = True End Function Hi Thanks Isabelle. I know where to put the first formula for the conditional formatting element , but I'm not sure how or where to put the Function code. Could you advise? Thanks or in another way, just execute this macro Sub MyCommentsMoreVisible() With Cells.SpecialCells(xlCellTypeComments) .Interior.ColorIndex = 33 End With End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
hi,
sorry Colin, forget the formula, it's a bad idea, because there's no recalculation even adding Application.Volatile but you can execute the macro -- isabelle Le 2011-04-29 21:34, Colin Hayes a écrit : I know where to put the first formula for the conditional formatting element , but I'm not sure how or where to put the Function code. Could you advise? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
In article , isabelle writes
hi, sorry Colin, forget the formula, it's a bad idea, because there's no recalculation even adding Application.Volatile but you can execute the macro Hi Isabelle OK Thank you. Could an extra line be added to the macro to return the cells to white if the comment is deleted? Thanks again. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
On Sat, 30 Apr 2011 00:09:32 +0100, Colin Hayes wrote:
Hi all Can someone advise how to using conditional formatting on cells containing a Comment? I'd be hoping to have some method of identifying these cells and then applying a colour or border format to make it more visible and obvious that they contain a comment. I'm finding the small red triangle a little hard to spot sometimes. Grateful for any help. It seems a macro would be OK from your other comments. Try this: To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ====================== Option Explicit Sub MarkComments() Dim c As Range With ActiveSheet.UsedRange .Interior.Color = xlNone .Font.Bold = False End With On Error Resume Next With Cells.SpecialCells(xlCellTypeComments) .Interior.Color = vbRed .Font.Bold = True End With End Sub ======================== |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
hi,
no, because problem is that adding or suppressing comments doesn't cause any events, so there's isn't exist way to detect this changes. -- isabelle Le 2011-04-29 22:10, Colin Hayes a écrit : In article , isabelle writes hi, sorry Colin, forget the formula, it's a bad idea, because there's no recalculation even adding Application.Volatile but you can execute the macro Hi Isabelle OK Thank you. Could an extra line be added to the macro to return the cells to white if the comment is deleted? Thanks again. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
You must run the macro manually,
to return the cells to white, see Run's answer -- isabelle Le 2011-04-29 22:44, isabelle a écrit : hi, no, because problem is that adding or suppressing comments doesn't cause any events, so there's isn't exist way to detect this changes. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional fomatting for cells containing a Comment
On Apr 30, 6:49*am, isabelle wrote:
You must run the macro manually, to return the cells to white, see Run's answer -- isabelle Le 2011-04-29 22:44, isabelle a écrit : hi, no, because problem is that adding or suppressing comments doesn't cause any events, so there's isn't exist way to detect this changes. hi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested, Conditional, IF formula - fomatting disappears | Excel Worksheet Functions | |||
Combine Cells and Retain Color Fomatting | Excel Discussion (Misc queries) | |||
Conditional Worksheet Fomatting | Setting up and Configuration of Excel | |||
Conditional Fomatting 3 in code | Excel Discussion (Misc queries) | |||
conditional fomatting | Excel Worksheet Functions |