Apache OpenOffice Community Forum

Posted: **Fri Feb 26, 2010 10:26 am**

I am looking for a way to test Calc cell background colors using a Macro.
I have been unable to find a reasonable document with lists of supported statements, syntax and examples. My Google searches find lots of examples of changing cell background colors, but nothing to determine what the current background color is, and use that value. I have also gone through both the Basic and the Programmer guide books with multiple searches without success. I saw lots of good stuff, but not what I need right now.

Also, is there a color index value you can use (like red = 9) instead of RGB?

For example, the code below sets cell background for A1 and A2 to yellow, A3 and A4 to red, and leaves A5 and A6 alone.

I would like to write code that changes yellow cells to red, red cells to “no color”, and “no color” cells to yellow. I think the default background color is Transparent?

My question is, what is the test cell background color statement and syntax, what does it return, and how can I use it below? How do I correct the code below?

Code: Select all

Sub Loop1
'

 Dim A As Integer
 Dim Mx As Integer
 Dim r As Integer
 Dim c As Integer
Rem    R is row    C is column    Mx is maximum row    A is a spare variable

  Dim oSheet as Object
  oSheet = ThisComponent.CurrentController.ActiveSheet

REM   A1 A2 Cells to yellow
cell = osheet.getcellbyposition(0,0)
cell.cellbackcolor = rgb (255,255,0)
cell = osheet.getcellbyposition(0,1)
cell.cellbackcolor = rgb (255,255,0)

REM  A3 A4 Cells to red
cell = osheet.getcellbyposition(0,2)
cell.cellbackcolor = rgb (255,0,0)
cell = osheet.getcellbyposition(0,3)
cell.cellbackcolor = rgb (255,0,0)

 Mx = 5
 r = 0
 c = 0
 While r <= Mx

Cell = oSheet.getCellByPosition(c, r)
   
if [TEST CELL COLOR HERE] = [YELLOW] then
cell.cellbackcolor = rgb(255,0,0)
     	
elseif [TEST CELL COLOR HERE] = [RED] then
         	REM  I think 255,255,255 is transparent?
cell.cellbackcolor = rgb(255,255,255)

elseif [TEST CELL COLOR HERE] = [TRANSPARENT] then
cell.cellbackcolor = rgb(255,255,0)

endif

r = r + 1
Wend

End Sub

Posted: **Fri Feb 26, 2010 1:41 pm**

[Calc, Basic] Introspective cell functions

Posted: **Fri Feb 26, 2010 4:43 pm**

Set background color of cell A1 of Sheet1 to for example yellow, A2 to red, A3 to whatever, etc, and run this:

Code: Select all

Sub Main
Dim Doc, MYSheet, MYCell As Object
Doc = thisComponent 
MYSheet = Doc.Sheets (0)
for rw=0 to 4
  MYCell= MYSheet.getCellByPosition(0,rw)
  MYSheet.getCellByPosition(1,rw).Value= MYCell.CellBackColor
next rw
End Sub

there you'll get values for colors, that you can use to set both background color or font color as:

Code: Select all

    If MYCell.CellBackColor=16776960 then
        MYCell.CellBackColor=16777215
    else...

Posted: **Sat Feb 27, 2010 10:47 am**

Thanks very much. I am sure this will work and will try it tomorrow. Just what I was looking for.

Apache OpenOffice Community Forum

[Solved] Testing Cell Background Color

[Solved] Testing Cell Background Color

Re: Testing Cell Background Color

Re: Testing Cell Background Color

Re: Testing Cell Background Color