OpenOffice.info

Code: Alles auswählen

Sub bedingte_zellformatierung()
'Kriterien und Vorlagen definieren
Dim kuv(1, 9) As String
'Bedingungen
kuv(0, 0) = "Klaus"
kuv(0, 1) = "Peter"
kuv(0, 2) = "Hans"
kuv(0, 3) = "Martin"
kuv(0, 4) = "Uwe"
kuv(0, 5) = "Michael"
kuv(0, 6) = "Dieter"
kuv(0, 7) = "Wilfried"
kuv(0, 8) = "Gerd"
kuv(0, 9) = "Dirk"

'Vorlagennamen
kuv(1, 0) = "klaus"
kuv(1, 1) = "peter"
kuv(1, 2) = "hans"
kuv(1, 3) = "martin"
kuv(1, 4) = "uwe"
kuv(1, 5) = "michael"
kuv(1, 6) = "dieter"
kuv(1, 7) = "wilfried"
kuv(1, 8) = "gerd"
kuv(1, 9) = "dirk"

Dim bedingtes_format as Object
range = ThisComponent.GetCurrentSelection.rangeAddress
With range
	a_s = .startColumn 
	a_z = .startRow 
	e_s = .EndColumn 
	e_z = .EndRow 
End With

Dim zellformat(3) as New com.sun.star.beans.PropertyValue
zellformat(0).Name = "Operator"
zellformat(0).Value = com.sun.star.sheet.ConditionOperator.EQUAL
zellformat(1).Name = "Formula1"
zellformat(2).Name = "StyleName"

blatt = ThisComponent.CurrentController.ActiveSheet
For i = a_s to e_s
	For j = a_z to e_z
		bedingtes_format = blatt.getCellByPosition(i, j).ConditionalFormat
		For k = 0 To 9 
			zellformat(1).Value = CHR(34) & kuv(0, k) & CHR(34)
			zellformat(2).Value = kuv(1, k)
			bedingtes_format.addNew( zellformat() )
		Next k
		blatt.getCellByPosition(i, j).ConditionalFormat = bedingtes_format
	Next j
Next i

Msgbox "Fertig - bedingte Zellformatierungen wurden eingetragen."
End Sub

Code: Alles auswählen

sub bereichshintergrund
odoc = thisComponent
osheet = odoc.currentcontroller.activesheet
obereich = odoc.getcurrentselection()
      StartSp = obereich.rangeAddress.startColumn
      StartZe = obereich.rangeAddress.startRow
      EndSp = obereich.rangeAddress.EndColumn
      EndZe = obereich.rangeAddress.EndRow
      msgbox (startSp)
for n = StartSp to Endsp
	for m = StartZe to EndZe
	ocell = osheet.getCellByPosition(n,m)
 	if oCell.string = ""  then oCell.CellBackColor = -1 'Farbe zurücksetzen
 	if oCell.string = "Fritz" then oCell.CellBackColor = RGB(0,255, 0) 'Direkte Zuweisung von Hintergrundf.
	if oCell.string = "Franz" then oCell.CellBackColor = RGB(250,255, 0)
	if oCell.string = "Karl-Otto" then oCell.Cellstyle = "deinVorlagenname" 'Zuweisung einer Zellvorlage
  ' if oCell.string = "A" then oCell.CellBackColor = RGB(255,0, 0)
  ' if oCell.string = "B" then oCell.CellBackColor = RGB(0,0,255)
  ' if oCell.string = "C" then oCell.CellBackColor = RGB(0,50,55)
		
	next m
next n
end sub