Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Nou pe simpatie:
LaraTaner
 | Femeie
24 ani
Prahova
cauta Barbat
24 - 52 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #1 |
TRaPModerator
Postari: 962
|
|
Long story short, here's the code that will fix it if you are using Excel tables:
- First, the macro finds the first formatted Excel table (ListObject) on the active worksheet
- Next, it counts the number of data rows in the table
- Then it identifies the first, second and last data rows in the table
- Next, it clears the conditional formatting from the second row, to the last row
- Then it copies the first data row, and pastes its formatting onto all the data rows (including the first row)
NOTE: If there is other formatting in the first row, it will also be copied to the other rows
Sub FixCondFormatDupRules()
Dim ws As Worksheet
Dim MyList As ListObject
Dim lRows As Long
Dim rngData As Range
Dim rngRow1 As Range
Dim rngRow2 As Range
Dim rngRowLast As Range
Set ws = ActiveSheet
Set MyList = ws.ListObjects(1) ' first table in the sheet
Set rngData = MyList.DataBodyRange
lRows = rngData.Rows.Count
Set rngRow1 = rngData.Rows(1)
Set rngRow2 = rngData.Rows(2)
Set rngRowLast = rngData.Rows(lRows)
With ws.Range(rngRow2, rngRowLast)
.FormatConditions.Delete
End With
rngRow1.Copy
With ws.Range(rngRow1, rngRowLast)
.PasteSpecial Paste:=xlPasteFormats
End With
rngRow1.Cells(1, 1).Select
Application.CutCopyMode = False
End Sub |
Source:
https://www.contextures.com/excelcondit ... oblem.html
|
|
| |
|
| #2 |
TRaPModerator
Postari: 962
|
|
More ways to prevent extra rules (same source site):
How to Prevent Extra Rules
Here are a few suggestions so you can prevent Excel from creating extra conditonal formatting rules.
INDIRECT Function
In the conditional formatting rules, instead of a simple reference to another row, use the INDIRECT function to create the reference. For example,
- Replace this formula: =$A2<>$A3
- With this formula: =INDIRECT("$A" & ROW()-1)<>$A3
In the revised formula, the reference to the cell above (A2) is created with INDIRECT.
Add New Data at End
Instead of inserting new rows within the existing data:
- Add new data at the end of the Excel table.
- Then, sort the table data, so it's in the correct order
Clear, Sort, Delete
Instead of deleting a row within the table:
- Select the row and press Delete, to clear the cells
- Then, sort the table data, so the cleared row goes to the bottom of the table
- Delete the blank row, or leave it there, for the next new entry
|
|
| |
|
