How do I figure out the starting and ending Row/Column for data on a spreadsheet?

[vek.m1234]

I want to write a Macro to iterate my cells on the SpreadSheet and set background colors for the cells based on some criteria. My understanding is I have to first manually/hardwire/specify the interested range of cells.
oRange = enSheet.getCellRangeByName(“A2:C6”)

Does the sheet have properties that could be useful vs randomly specifying a large range to grab a large address-space and then iterating to look for text and heuristically determining my row/columns/address-space.

I tried Print oRange.StartColumn, oRange.EndColumn but that didn’t work and Xray oRange did not display anything relevant… except ColumnLabelRanges

[anon73440385]

… and set background colors for the cells based on some criteria.

Just for curiosity: Why don’t you consider to use Conditional Formatting for that task?

[vek.m1234]

https://i.imgur.com/sgur1lC.png so this is my sheet currently - I want to color vegetables one color, dairy a different color and so on AND I DON’T want to do it manually… veggie = [ ‘eggplant’, ‘carrot’ ] color = { ‘veggie’ : ‘blue’ } I can’t do data structures using conditional formatting and then I would need to use regex for match(r’egg+’, Sheet1.A1)

[anon73440385]

on AND I DON’T want to do it manually.

Conditional Formatting is not manual, but automatic formatting of cells based on conditions (to be implemeted once). I don’t see how this could be easily setup using macros especially when it is not clear how your questions should support this task. Of course a macro could evaluate .StartColum,.StartRow, .EndColumn and .EndRow of either UsedArea or CurrentSelection but i don’t see what it’s worth for.

Sub UsedArea
 
 Dim oDoc 		As Object
 Dim oSheet 	As Object
 Dim oCursor 	As Object
 Dim oCell  	As Object
 
 oDoc = ThisComponent
 oSheet = oDoc.CurrentController.ActiveSheet
 
 oCursor = oSheet.createCursor()
 oCursor.gotoStartOfUsedArea(False)
 oCursor.gotoEndOfUsedArea(True)
 
 StartCol=oCursor.RangeAddress.StartColumn
 StartRow=oCursor.RangeAddress.StartRow
 EndCol=oCursor.RangeAddress.EndColumn
 EndRow=oCursor.RangeAddress.EndRow
 
End Sub

[vek.m1234]

How do you create a conditional formatting rule for a long list of items - can you use AND OR NOT or regex-patterns: I cannot write “egg” OR “milk” as a condition for “contains” It seems to be very basic for numbers mostly: less than 10 (ah! thank you - UsedArea)