AW: Feiertage ausrechnen und Datumsangaben markieren.
05.07.2006 13:04:58
Werner
Hallo Picasso
Nach dem Import der Daten folgenden Code starten.
Sub TESTEN()
Columns("B:B").NumberFormat = "ddd* dd/mm/yyyy"
For i = 1 To Range("B65536").End(xlUp).Row
If Len(Cells(i, 2)) > 10 Then
Cells(i, 18).FormulaR1C1 = "=LEFT(RC[-16],FIND("" "",RC[-16])-1)*1"
Cells(i, 2) = Cells(i, 18)
Cells(i, 18) = ""
End If
Next i
Range(Cells(3, 2), Cells(Range("B65536").End(xlUp).Row, 2)).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WOCHENTAG(B3;2)>5"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 2
End With
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ODER(ZÄHLENWENN(NATIONAL;B3)>0;ZÄHLENWENN(REGIONAL;B3)>0)"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(2).Interior.ColorIndex = 4
[A1].Select
End Sub
Teste es im Anhang
https://www.herber.de/bbs/user/34858.xls
Gruss Werner