pátek 17. června 2011

Rozšířené podmíněné formátování

Možná jste se již setkali s funkcí podmíněné formátování (dále PF), které umožňuje stanovit formát buňky na základě daných podmínek (obsahu buňky). Tak můžeme například Excelu říct, že pokud bude buňka A1 obsahovat číslo menší než 10, ohranič buňku červenou čarou. Ve všech ostatních případech zůstane buňka bez ohraničení.

Zatímco MS Excel 2007 a novější již umožňují zadat více kritérií, verze 2003 povolí zadat maximálně 3 podmínky. Mnohdy však potřebujeme podmínek zadat více. Jak se s tímto vypořádat?

Stáhnout vzorový sešit (xls, 30 kB), 25. 3. 13

Postup

Pro formátování dle více kritérií využijeme funkce VBA Select ... Case. Zatímco příkaz .Interior.ColorIndex nastaví barvu pozadí buňky, .Font.ColorIndex se postará o správnou barvu písma.
Makro je funkční, avšak prozatím neefektivní - pokusím se jej předělat v nejbližší době.
  • Předpokládejme, že už máme v sešitu oblast buněk, kterou budeme chtít formátovat.
  • Nyní si v jiné oblasti oblasti buněk vytvoříme "vzorkovník", podle kterého bude naše oblast formátována.
  • Budeme-li chtít například použít 6 formátů buněk, ve vzorkovníku naformátujeme 6 buněk (každou s jedním formátem). Obsah buněk ve vzorkovníku by měl odpovídat podmínkám - viz obrázek vpravo.
  • Do sešitu vložíme nový modul do kterého umístíme následující makro.

Option Explicit
Sub formát()
Application.ScreenUpdating = False
Dim bunka As Range
Dim oblast As String
 oblast = "z_přehled" '<<< oblast, která bude formátovaná

'vymaže předchozí formátování
With Range(oblast)
 .Interior.ColorIndex = xlNone
 .Font.ColorIndex = 1
End With

'naformátuje pozadí buňky a barvu textu v oblasti podle buněk A2:A7

For Each bunka In Range(oblast)
 With bunka
 Select Case .Value
 Case 1
 .Interior.ColorIndex = Range("A2").Interior.ColorIndex
 Case 2 
 .Interior.ColorIndex = Range("A3").Interior.ColorIndex
 Case 3 
 .Interior.ColorIndex = Range("A4").Interior.ColorIndex
 Case 4 To 8 
 .Interior.ColorIndex = Range("A5").Interior.ColorIndex
 Case 9 To 20 
 .Font.ColorIndex = Range("A6").Font.ColorIndex
 .Interior.ColorIndex = Range("A6").Interior.ColorIndex
 Case "-" 
 .Interior.ColorIndex = Range("A7").Interior.ColorIndex
 End Select
 End With
Next bunka
 
Application.ScreenUpdating = False
End Sub

  • V makru nastavíme správnou oblast, která se bude formátovat a buňky, které tvoří vzorkovník (v tomto případě A1:A7).
  • Makro spustíme.