Macro To Delete Comments And Unhighlight Cells

Oct 24, 2007

I am having trouble writing a macro to do the following. Someone had created a spreadsheet a while back that highlighted the cell green and inserted a comment with the person's name and date/time if they changed anything in that cell. The macro works great, but I need a macro to undo the changes.

I would like a macro that will remove all comments from the sheet and revert the cells back to their original color. Since the cells encompass many colors, at the very least, I would like the macro to remove the comments and make the cells the default shading (white).

View 9 Replies


Excel 2007 :: What Series Of Cells To Unhighlight

Nov 9, 2012

I have excel 2007, I have this button that works perfect, except, I want to tell it what series of cells to unhighlite. I want it to only unhighlite cells b11:h9858

Private Sub CommandButton3_Click()
Me.Unprotect Password:="123"
Cells.Interior.ColorIndex = xlNone
Application.GoTo Range("A11"), True
Me.Protect Password:="123"
End Sub

View 2 Replies View Related

Adding Comments To Cells Via Macro

Oct 8, 2009

When adding a comment I check for an existing comment, if exist then delete and add new comment and some text in that cell. This works as long as the cell has an existing comment; other wise it advances to the "Else" where I thought I would be entering a comment and text in an empty cell. Why does my macro treat an empty cell as if it has a comment?

View 4 Replies View Related

Macro To Convert Range Of Cells To Comments

Jul 12, 2007

Reference the archives at:

How can I modify this sub to skip cells that are empty? I only want cells that are populated within the range to actually display the comment indicator, with the cell text transferred to the comment.

Ideally, I'd also like the text to be moved (not just copied) to the comment and leave an 'X' in the cell, in place of the text that was transferred to the comment, but I'll settle for the action above.

View 9 Replies View Related

Unable To Delete Or Edit Comments

Feb 9, 2009

I have a comment which I wish to edit, but I am unable to either edit or delete the comment. I have checked the 'shared workbook', 'shared worksheet', and 'protected cell' options and they are all turned off (never had them on).

The cell I have the comment on has a dropdown box, but I can't see that being a problem. I am able to add a comment, which I can edit and delete, but the original comment seems to be 'stuck'.

View 3 Replies View Related

Comments From Dropdown List - Delete If Cell Value Deleted

Sep 11, 2013

I created comments to be shown when drop down list items are selected using the helper cell and workbook change solution from Leith Ross at the thread below.


This works perfectly to show the comments. What I'd now like to do is delete the comment if the content of the cell is deleted.

View 5 Replies View Related

Is There A Method To Globally Delete/remove All Comments From A Worksheet?

May 19, 2006

I'm attempt to automatically validate QTP spreadsheets with an EXCEL VBA macro. All is going well but I now want to look smarter. Is there a method to globally remove all comments from a worksheet?: D What I actually want to do is add comments to cells which fail validation but some of these cells may already contain comments. If they do contain comments then I want to overwrite with my comments. I thought the easiest way would be to delete all comments at the start of macro but I am all ears to any other approach.

View 4 Replies View Related

Prevent Copy / Paste Cells With Comments Or Allow Paste But Do Not Paste Comments

Feb 4, 2014

How would you prevent the copy/paste of cells that have comments?

Also, how would you allow cells with comments to be copied and pasted without pasting the comments?

I also have an aside question about the forum advanced search. When searching for multiple search words, how would you type the search to include all words, for example, "prevent" & "paste" & "comments".

View 7 Replies View Related

Add Cell Comments Macro

Jan 2, 2007

I am trying to add comments in a selection of cells and where there is a value then the comment should show the formula value of the cell ie if the formula in cell A1 is = 9 + 10 then the comment should show this. This I have got to work, but if I have a comment already the macro fails and I have tried to get round this but I am not clever enough.

Sub InsertingComments()

Dim r As Range

For Each r In Selection
If r.Comment = True Then

View 9 Replies View Related

Macro To Get Comments From All Worksheets

Feb 10, 2007

I am trying to find out if it is possible to do the following

I have an excel workbook with comments in a number of worksheets. I have created a macro so I can list the comments and the value of the cells with comments. The macro creates a new worksheet and list the comments and value this great but I can only get it to create the comments from the sheet which is active. Is there a way of getting all the comment and cell value from all the worksheet (the entire workbook)

this is the code for the macro

Sub shoppinglist()
Application. ScreenUpdating = False

Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
On Error Goto 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If ...

View 6 Replies View Related

Macro To Delete Cells

Jun 23, 2009

I am creating a macro that pulls data from a pivot table and organizes it on the same sheet as the pivot table. Once I get my data organized, there are several rows that contain data that I dont need and I want my macro to delete this info.

The catch is, since I have the macro placing data on the same sheet as my pivot table I cannot just delete the whole row so I am trying to think of a way to identify the rows that have unusable data and then have the macro just delete the cells in that row without affecting the pivot table.

I used a formula in the last column of my wks to mark unnecessary rows with a "D". Is there a way I can program the macro to identify the "D" and then delete a selected range of cells in that row - and then have it loop through all the rows of the spreadsheet?

View 9 Replies View Related

Having Certain Cells Delete With Macro

Dec 4, 2009

i have a report where information is entered via a macro, i would like the cells to wipe of any data when another cell is clicked

in practice i will have a cell labelled "clear" the user will click it and a message box will promtp " would you like to clear pam sheet" upon the user clickin no of course nothing ill happen, when they click yes certain cells will clear

some are a range and some are individual cells

View 9 Replies View Related

Deleting Comments In Blank Cells

May 6, 2009

Is there a way to delete comments in a selected range of blank cells?

View 7 Replies View Related

Insert Comments In Merged Cells?

Apr 20, 2012

The macro below only works for a single cell, how to include merged cells?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
On Error Resume Next
Dim cmtText As String


View 2 Replies View Related

Format Cells With Cell Comments

Nov 16, 2006

1)After comparison and no difference between the cells
1.1) If the cell selected is blank then there will not be any color
1.2) Else the cell will be colored GREEN

2)After comparison and there is difference between the cells
2.1) Cells wiill be colored RED

However, my code seems to be unable to execute (1.1) . Pls help to see if there's any thing wrong with my code

For K = 1 To 31
For L = 1 To 29
'Do a comparison of the cells
If StrComp(Array_compare(A, 1), Array_compare(A, 2)) = 0 Then

If IsEmpty(Array_compare(A, 1)) Then
Cells(K, L) = Array_compare(A, 1)

Sheets(3).Select .................

View 9 Replies View Related

Move Cell Comments With Cells

May 3, 2008

I have a cell comment on a series of columns and want to show only one column at a
time. For this reason I hide the ones not used, but the cellcomment doesnt move and
is still at the original place. How I move the comment when the original colums between are hidden ?

View 2 Replies View Related

Macro To Show & Hide Comments

Jul 24, 2007

I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.

View 6 Replies View Related

VBA Macro To Delete All Columns That Have Cells Containing Value

Dec 19, 2012

For now, I have data in Column A to E. I have the first row (A1 to E1) contaning different numbers. I need a macro that delete all collumns that cells (in A1 to E1) contain number 1. Next month, I may have 10 columns (A to J) and I want to run the same macro that delete all columns that A1-J1 containing value of 1. The number of columns changes every month, so I want a macro that work to the last column without the need to specifying that column range.

I was using this, but did not work propery (since It just deletet every other column & have to run it a few times to complete & need to specify column range):

Range(A1:E1).Select For each cell in selection If cell.Value>1 Then
Cell.EntireColumn.Delete End If Next cell

View 2 Replies View Related

Delete Row Macro (shift Cells Up Then)

Jul 1, 2007

I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?

View 9 Replies View Related

Macro To Delete Cells With #N/A Errors

Aug 4, 2009

i have a column C with thousands of data. in that column, i had done a vlookup formula based on the lookup_value on its right, that is column B. some of the values arent available in the mapping table, causing the #N/A error.

i want to be able to delete those #N/A errors so that my dataset looks 'cleaner'. its tiring for me to go through each row one by one and delete them manually. i wnat to create a macro but im not so sure how to start.

in pseudo-code, it shud be something like this:

delete cell if cell_value = #N/A error.

View 9 Replies View Related

Comment Feature Without The Red Triange On Cells With Comments

Jul 22, 2009

I'd like to know how to have the comment feature without the red triange on cells with comments. I didn't want to use data validation comments because I want the hover feature.

View 5 Replies View Related

Edit Comments Not Working For Certain Cells In A Column

Aug 28, 2009

I try to edit comments in cells of a certain column and nothing happens. I am able to edit other comments throughout the spreadsheet, but when I select "edit comments" for these particular cells, no edit box is displayed for editing. It worked fine a couple of weeks ago.

View 11 Replies View Related

How To Give Comments To Multiple Cells At A Time

Feb 4, 2012

columns consist of 1,2

I selected only 1 value of cells

I want to add comments to all the 1 value cells by selecting all 1 by shift + ctl +down

I can put same comments to all the cells?

If you want to have the same comment applied on many cells you could apply it to a single cell, then use the copy and paste special tool bar commands (under Edit) In Paste Special select "Comments" but value is going of that cell.

View 5 Replies View Related

Change Cells Comments Commented By Text

Dec 28, 2008

I noticed that I have several (@200) comments that when visible displays Cell X Commented by (my work user number) in the lower left corner of the excel window. I'm not sure how to change this so its the company name instead of my work number. I'd be using a loop to go through each comment but need help with the line that would change this part.

View 8 Replies View Related

Transfer Cells Values To Cell Comments

Aug 22, 2007

I have a report where, month in month out, i have to append columns on the right, to give a Year to date figure. Now there are many rows with different numbers but one of the columns is call [NOTES]. This is where the user can provide commentry on the weekly figures.

Now whats getting annoying is i have to copy this commentry and then create a comments box then paste the text in there and finally, hide the comment.

So what i want to be able to do is be able to Copy the cell, then, have a right click menu button saying "Paste As Comment", so it paste the selected cells contents into a comment and hides it.

View 9 Replies View Related

Store Values Of Cells With Cell Comments

Jul 1, 2008

I have a worksheet where some cells contain a comment. I don't know beforehand how many of those cells are present, nor their address. I want to write a macro that stores the values of only the cells that contain a comment into an array (of course the size of the array is not known beforehand). This should be done by scanning through those special cells in a given order (by rows, by columns, whatever).

View 4 Replies View Related

Delete Cells Macro Compile Error

Feb 11, 2010

I am creating some code to unprotect a workbook, unhide columns, delete a series of cells, rehide columns and then reprotect the workbook:

View 14 Replies View Related

Macro To Delete Rows In Which All Cells Contain Same Or Single Value?

Sep 3, 2013

I need a macro to delete each row in which all cells contain the same value, always starting in cell G2 and going out an indefinite number of cell's. So, for one run, the range could be G2:BU2 and another it could be G2:PW2. Also, the number of row's is indefinite.

And an added complexity: I need it to ignore the cell in this search process if the cell contains "NC" - so if all the data in the row is the same except for a few that say NC - then it gets deleted. If the any of the cells in the row have other values, then that row does not get deleted - even if it contains "NC".

View 2 Replies View Related

Macro To Delete Rows Where There Are Blank Cells

Sep 25, 2013

I would like code that will delete all rows where there are blank cells in Col D from row2 onwards.

View 2 Replies View Related

Autosize Cell Comments/Shapes Via Macro Code

Aug 25, 2006

Among many attempts to insert and autosize a comment using VBA manually, which errored, i recorded the following bit. When running the macro, it does format the cell - text, not the comment - text and errors in the line highlighted in red.

Range("A14").Comment.Visible = False
Range("A14").Comment.Text Text:="Ladida"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
.AutoSize = True
End With

I just want to add

.AutoSize = True

or something to that effect to the bit below. Any suggestions?

Range("A14").Comment.Text "Ladida"

View 5 Replies View Related

Copyrights 2005-15, All rights reserved