MS Excel

Téma: Makro - smazani pravidel podmineneho formatovani

Zaslal/a siruk 6.5.2021 19:04

Zdravim ve spolek,

chtel bych poprosit o radu jestli je mozne a pripadne jak, pomoci makra smazat na listu aktualni pravidla podmineneho formatovani a nastaveni novych pevne definovanych podminek.
Jde mi o to, ze mame na oddeleni tabulku, ve ktere se zaroven pri kopirovani bunek, kopiruji i pravidla pro podminene formatovani a po case je sesit dost prehlcen.

Dekuji za rady a tipy.

Michael

Zaslat odpověď >

kabaka(7.5.2021 7:26)#050610

pozri toto

https://cs.extendoffice.com/documents/excel/3686-excel-remove-conditional-formatting-but-keep-format.html citovat

siruk(7.5.2021 9:24)#050612

kabaka napsal/a:

pozri toto

https://cs.extendoffice.com/documents/excel/3686-excel-remove-conditional-formatting-but-keep-format.html

Díky, uz jsem to nejak zmastil. Pokud by mel nekdo navrh na vylepseni, budu jen rad.

Sub Makro8()
'
' Makro8 Makro
' MSI-11064 07/05/2021
' Purpose of this macro is optimize and clear mess format conditions, that is created during copy cells.
'The code delete format conditions on selected area and apply hard coded conditions below.

'
'select range Q16:ZZ880
Range("Q16:ZZ880").Select

'delete format conditions from range Q16:ZZ880
Range("Q16:ZZ880").FormatConditions.Delete

'"=POSUN(Q16;0;1)=""x"""
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=POSUN(Q16;0;1)=""x"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'if string value is 'x' then green
Selection.FormatConditions.Add Type:=xlTextString, String:="x", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'if 0,001> x <10000
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0,001", Formula2:="=10000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13434828
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

'if x<0
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16777024
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13421823
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Subcitovat

siruk(7.5.2021 13:09)#050618

kabaka napsal/a:

pozri toto

https://cs.extendoffice.com/documents/excel/3686-excel-remove-conditional-formatting-but-keep-format.html

Vyzkousel jsem, ale bohuzel to neni pro muj ucel pouzitelne, potrebuji pravidlo aplikovat 273 tisic bunek, a tento VBA skript (v odkaze) mi zamestnal muj laptop s Intel Core i7-7500U CPU @ 2.90 GHz cca na 10 minut