Come back after working on another project and interesting things have happened.
First of all I want to thank everyone who participated in answering this question, especially pierre-yves samyn who’s answer I went with. This was my first time posting a question to a forum, I’m normally able to find answers to whatever questions I have by googling, thanks again for your patience with me.
What follows is just a bit of code to setup an example table, skip below it to find my take on pierre’s answer.
load code into empty spreadsheet
sub createTable
sheet = thiscomponent.sheets(0)
'load some data
data = array(array("Team","BOS","CHI","HOU","KC"),array("Boston","","CHI","HOU(1)","KC"),_
array("Chicago","CHI","","CHI","Tied"),array("Houston","HOU(1)","CHI","","Tied(1)"),_
array("Kansas City","KC","Tied","Tied(1)",""))
sheet.getCellRangeByName("A1:E5").DataArray = data
'make some ranges
refPos = sheet.getCellRangeByName("A1").cellAddress
addrs = sheet.getCellRangeByName("B2:E5").absoluteName
if sheet.NamedRanges.hasByName("TeamTable") then sheet.NamedRanges.removeByName("TeamTable")
sheet.NamedRanges.addNewByName("TeamTable", addrs, refPos, 0)
addrs = sheet.getCellRangeByName("B1:E1").absoluteName
if sheet.NamedRanges.hasByName("Teams") then sheet.NamedRanges.removeByName("Teams")
sheet.NamedRanges.addNewByName("Teams", addrs, refPos, 0)
'load some crosses
border = createUnoStruct("com.sun.star.table.BorderLine")
border.Color = RGB(0,0,128) 'blue
border.OuterLineWidth = 26 '26=.75pt, 35=1.00pt
table = sheet.getCellRangeByName("TeamTable")
for pos = 0 to table.Rows.Count-1
cell = table.getCellByPosition(pos, pos)
cell.DiagonalTLBR = border : cell.DiagonalBLTR = border
next
'prettify stuff
table.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
sheet.getCellRangeByName("Teams").HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
sheet.getCellRangeByName("A1:E1").CharWeight = com.sun.star.awt.FontWeight.BOLD
'make some styles
cellStyles = thiscomponent.StyleFamilies.getByName("CellStyles")
styles = array(array("Positive", RGB(0,153,0)), array("Tied", RGB(128,0,128)),_
array("Negative", RGB(255,0,0)))
for each style in styles
if cellStyles.hasByName(style(0)) then cellStyles.removeByName(style(0))
cs = thiscomponent.createInstance("com.sun.star.style.CellStyle")
cellStyles.insertByName(style(0), cs) : cellStyle = cellStyles.getByName(style(0))
cellStyle.ParentStyle = "Default" : cellStyle.CharColor = style(1)
next
end sub
What follows is, largely, the code I implemented. This code creates mutliple conditions on a range.
sub formatTable
'get stuff you just made
sheet = ThisComponent.Sheets(0)
table = sheet.getCellRangeByName("TeamTable") : teams = sheet.getCellRangeByName("Teams")
tableColumns = table.Columns.Count-1 'columns rows doesn't matter, table is square
dim empty() as new com.sun.star.beans.PropertyValue 'create an empty property
for r = 0 to tableColumns
row = table.getCellRangeByPosition(0,r,tableColumns,r) 'get row of table
'creates a conditional range format and an empty entry format for the row range,
'clear is optional, it's just there so that this sub can run multiple times
cf = row.ConditionalFormat : cf.clear : cf.addNew(empty) : row.ConditionalFormat = cf
'get sheet conditional range format you just made, formats are added to a list,
'most recent format is at the bottom of the list. if you're adding a condition entry
'to an existing range you'll have to use pierre's absolute name range thing
i = sheet.ConditionalFormats.Length-1
cfs = sheet.ConditionalFormats.ConditionalFormats(i)
'get the condition entry you just made and load your stuff
cfe = cfs.getByIndex(cfs.Count-1)
cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
cfe.setPropertyValue("StyleName", "Positive")
'create another entry, no need to re-get range since you haven't changed it
cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
cfe = cfs.getByIndex(cfs.Count-1)
cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
cfe.setPropertyValue("Formula1", """Tied""")
cfe.setPropertyValue("StyleName", "Tied")
'again only different
cf = row.ConditionalFormat : cf.addNew(empty) : row.ConditionalFormat = cf
cfe = cfs.getByIndex(cfs.Count-1)
cfe.setPropertyValue("Operator", com.sun.star.sheet.ConditionFormatOperator.NOT_CONTAINS)
cfe.setPropertyValue("Formula1", """" & teams.getCellByPosition(r,0).string & """")
cfe.setPropertyValue("StyleName", "Negative")
next
end sub
I found methods createByRange and createEntry in the ConditionalFormats, but was unable to made them work, so I went with the old addNew method to create my formats, perhaps someone will come by and show us how it’s supposed to be done. Anyway, I’m marking this question SOLVED, thanks again you all.