Enter the formula =COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1)>1Ħ. Select 'Use a formula to determine which cells to format'.ĥ. On the Home tab, in the Styles group, click Conditional Formatting.Ĥ. To find and highlight duplicate rows in Excel, use COUNTIFS (with the letter S at the end) instead of COUNTIF.Ģ. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight names that occur more than 3 times. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference. Excel automatically copies the formula to the other cells. Always write the formula for the upper-left cell in the selected range (A1:C10). Excel highlights the triplicate names.Įxplanation: = COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. Select 'Use a formula to determine which cells to format'.Ħ. On the Home tab, in the Styles group, click Conditional Formatting.ĥ. First, clear the previous conditional formatting rule.ģ. Execute the following steps to highlight triplicates only.ġ. When I click to debug, this is what comes up: (the line of code that is highlighted in the debug code window is underlined below): Private Sub Document_New()ĭim wasProtected As Boolean Dim SectionAbove As Integer 'SectionAbove = current section minus one SectionAbove = CInt(Selection.Information(wdActiveEndSectionNumber)) - 2 'See if section above was protected, and unprotect If ActiveDocument.Sections(SectionAbove).ProtectedForForms = True Then ActiveDocument.Sections(SectionAbove).ProtectedForForms = False wasProtected = True End If 'Go back one section Selection.GoTo What:=wdGoToSection, Which:=wdGoToPrevious, Count:=6 'Copy current page ActiveDocument.Bookmarks("\Page").Range.Copy 'Go forward one section Selection.GoTo What:=wdGoToSection, Which:=wdGoToNext, Count:=5 'Paste copied page Selection.Paste 'Reprotect if section was protected If wasProtected = True Then ActiveDocument.Sections(SectionAbove).By default, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), etc. When I protect the document, the error message that I get is: Run time error 5887 Command not available because document is passowrd protected. I have adjusted the section count so that it works to how the document is laid out, and runs with the correct outcome - copying the page to a new page. Hi JFK555, again, thank you for the code that you have put up here - I have tried it, and when the document is not protected it still works, copies the page above to the new page and moves the other pages down. Selection.GoTo What:=wdGoToSection, Which:=wdGoToNext, Count:=1ĪctiveDocument.Sections(SectionAbove).ProtectedForForms = True Selection.GoTo What:=wdGoToSection, Which:=wdGoToPrevious, Count:=1ĪctiveDocument.Bookmarks("\Page").Range.Copy If ActiveDocument.Sections(SectionAbove).ProtectedForForms = True ThenĪctiveDocument.Sections(SectionAbove).ProtectedForForms = False 'See if section above was protected, and unprotect SectionAbove = CInt(Selection.Information(wdActiveEndSectionNumber)) - 1 'SectionAbove = current section minus one The following would work in that scenario but not sure if that is what you mean. the page above) that you are trying to copy is protected meaning it can't be copied, then your code can unprotect it before you copy it. If you insert a section break and the section (i.e. Do you mean that the checkbox will insert a section break rather than a page break? And that the page that you then need to copy is protected? If you are using section breaks, then you need wdGoToSection rather than wdGoToPage.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |