PDA

Vollständige Version anzeigen : Blockpfeil automatisch verschieben?


sling
08.07.2015, 14:37
Hallo, wir haben in einer größeren Abteilung einen alterstümlichen Terminkalender in dem die Anwesenheiten der einzelnen MA dokumentiert sind. Horizontale Einteilung in Monate/Wochen/Tage. Jetzt ist die aktuelle Woche zur besseren Navigation mit einem Blockpfeil markiert. Leider muss der jeden Montag manuell um 7 Spalten nach rechts verschoben werden.
Kann man diesen Vorgang irgendwie einfachst automatisieren? Gibt es bessere Möglichkeiten z.B. automatische farbliche Markierung des aktuellen Wochenfeldes?
Gruß Stefan

EarlFred
08.07.2015, 14:46
Hallo Stefan,

für sowas bietet sich die Bedingte Formatierung an.
Bitte entweder selbst recherchieren (es gibt zahllose Beispiele im Netz zu finden) oder die Mappe (auszugsweise) hochladen, selbstredend ohne sensible Daten.

Grüße
EarlFred.

Beverly
09.07.2015, 08:30
Hi,

du könntest für die bedingte Formatierung als Vergleich die Kalenderwoche verwenden:

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:30px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /><col style="width:67px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td><td>P</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">01.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">02.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">03.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">04.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">05.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">06.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">07.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">08.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">09.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">10.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">11.07.2015</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">12.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">13.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">14.07.2015</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">15.07.2015</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">MA 6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="; color:#000000; border-color:#000000; background-color:#C6E0B4 ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="11" > Bedingte Formatierung Haupttabelle 1 </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td > Wird angewendet auf &nbsp; </td><td> Nr. Bed. *Regeltyp&nbsp; </td><td> Operator &nbsp; </td><td> Formel1 &nbsp; </td><td> Formel2 &nbsp; </td><td> Format Schrift Füllfarbe &nbsp; </td><td> Unterstrichen &nbsp; </td><td> Schrift- farbe </td><td> Muster &nbsp; </td><td> Musterfarbe </td><td> Typ </td><td> Bereich </td></tr><tr><td>$B$1:$P$7</td><td style="text-align:left; ">01.Bed.: Formel ist</td><td>&nbsp;</td><td style="text-align:left; ">=KALENDERWOCHE(B$1;21)=KALENDERWOCHE(HEUTE();21)&nbsp;</td><td>&nbsp;</td><td style="background-color:#C6E0B4; ;text-align:right " >11854022</td><td style="text-align:left; "> &nbsp; </td><td>&nbsp;</td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">B1:&nbsp;P7</td></tr></table><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; width:800px; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td colspan="5" > Bedingte Formatierung Haupttabelle 2 </td><td colspan="3" > oberere/unterer Bereich </td><td> &nbsp; </td><td>&nbsp;</td></tr><tr valign="top" style="background-color:#CCFFFF; text-align:center; font-weight:bold; "><td> Wird angewendet auf &nbsp; </td><td> Nr. Bed.&nbsp;</td><td> Format Zelle </td><td> Anhalten </td><td> Auswahl &nbsp; </td><td> Anzeige &nbsp; </td><td> Anzahl &nbsp; </td><td> Durchschnitt &nbsp; </td><td> Typ &nbsp; </td><td> Bereich </td></tr><tr><td>$B$1:$P$7</td><td>01.Bed.</td><td> &nbsp; </td><td style="text-align:left; ">Falsch&nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td> &nbsp; </td><td style="text-align:right; ">2</td><td style="text-align:right; ">B1:P7</td></tr></table><b>Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.</b><br/><br/><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 19.06 einschl. 64 Bit</td></tr></table><br/>

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

Frank Furter
09.07.2015, 12:32
Hi Karin, der TE gibt die version XL2007...