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

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
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 =
	ElseIf IsCellSubSubHdr(txt) Then
		Print "Green"
		oCell.CellBackColor = nGreen		
		oCell.CellBackColor = nWhite
	End If
	'Print i, txt

End Sub

Function IsCellSubSubHdr(ByVal txt) As Boolean
Dim aSearchResult
Dim oTextSearch: oTextSearch = CreateUnoService("")
Dim aSrcOpt As New
Dim enLocale As New

With aSrcOpt
	.searchFlag =
	.Locale = enLocale
	.algorithmType =
	.searchString = "[a-zA-Z ]+"
End With
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

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

txt = oCell.getString()

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

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!!)

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



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 Descrição da imagem to the left of the answer, to finish the question.