How do I apply a conditional to all cells old and new in the sheet?

[vek.m1234]

So I want to automatically apply a conditional to all cells in the sheet and color them appropriately without manually doing it. All ### are Red and ## are Green and plain text in first column is Blue - any way besides writing a Macro?

Macro that works somewhat… “Basic runtime Error: Argument not optional” It might have something to do with ‘txt’ that is ByVal to match function IsSubSubCellHdr

REM  *****  BASIC  *****

Sub Main
ColorFirstColumn()
End Sub

Sub ColorFirstColumn
Dim nRed As Long: nRed = RGB(255, 181, 197) 'Pink1
Dim nGreen As Long: nGreen = RGB(144, 238, 144) 'light green
Dim nBlue As Long: nBlue = RGB(174, 238, 238) 'PaleTurquoise2
Dim nWhite As Long: nWhite = RGB(255, 255, 255) 'White

Dim oDoc as Object



oDoc = ThisComponent
For rowno=0 To 500
	oCell = oDoc.Sheets(0).getCellByPosition(0,rowno)
	txt = oCell.getString()
	If (InStr(txt, "###")) Then
		oCell.CellBackColor = nRed
	ElseIf (InStr(txt, "##")) Then
		oCell.CellBackColor = nBlue
		oCell.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
	ElseIf IsCellSubSubHdr(txt) Then
		Print "Green"
		oCell.CellBackColor = nGreen		
	Else
		oCell.CellBackColor = nWhite
	End If
	'Print i, txt
Next


End Sub

Function IsCellSubSubHdr(ByVal txt) As Boolean
Dim aSearchResult
Dim oTextSearch: oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
Dim aSrcOpt As New com.sun.star.util.SearchOptions
Dim enLocale As New com.sun.star.lang.Locale

With aSrcOpt
	.searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED
	.Locale = enLocale
	.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
	.searchString = "[a-zA-Z ]+"
End With
oTextSearch.setOptions(aSrcOpt)
aSearchResult = oTextSearch.searchForward(txt, 0, Len(txt) - 1)
'Print "foo", aSearchResult.subRegExpressions
If aSearchResult.subRegExpressions > 0 Then
	IsCellSubSubHdr = True
	Exit Function
End If	
IsCellSubSubHdr = False
End Function

[robleyd]

You don’t mention where the error occurs. But…

txt = oCell.getString()

If the cell is numeric, or empty, what will txt contain?

[vek.m1234]

Oh purr works now - yeah, got that fixed - it’s all these weird things that keep biting me in the rump. (The editor cursor has an affect on where the program starts - wth!!)

[Wanderer]

Your task can be done by conditional formatting directly. All you have to do is translating your condition in regukar funktions SEARCH for calc, define 3 cell-formats - to be used if the condition apply - for the colour and select the range for the condition a1:a10000 as example.

Wiki from Openoffice

J.

[schiavinatto]

@vek.m1234,

Macro is not necessary, it can be by Conditional Formatting, see test file.

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.