[Solved] Basic macro to color cell background

[Pedroski]

I would like to have a basic macro which looks at a file and searches each cell for the content F If it finds F, it should colour the cell background red.

Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("class1_2012")

for c = 1 to 3
for r = 1 to 3

Can you help me fill in the rest?? Thanks!

[kingfisher]

How many cells do you have to search and how many cells containing 'F' do you expect to find? The find tool may suffice together with a cell style which you could apply to any cell found.

The find tool is usually faster than such a macro. On the Find dialogue tick the boxes labelled "Match case" and "Entire cells." You need the latter if you want to find only cells which contain only the letter F. Have the styles box open as well (F11) and when a cell is found click the cell style you have created with a red background.

To create a cell style, F11 then right-click 'Default' and select 'New'.

[kingfisher]

Since writing the above, it occurs to me that you can use the "Find All" button and change the style of all cells found at once.

[Villeroy]

The built-in feature Format-->Conditional Formatting does the job.
Define a red cell style, select the range in question, call the dialog where you set <Cell Value> <equals> "F" (with quotes) and your cell style to apply.
This will change the style automatically (without calling any macro) whenever a value changes to or from "F".

[Pedroski]

Yeah, thanks, but it would really be more convenient to have a macro. I have 3 German and 5 English classes. I give them lots of little tests, which, if they get F they have to repeat. I enter the number of mistakes by hand. If they make more than 5, a formula puts an F in the neighbouring cell, otherwise P. When I put that on the overhead projector, it is not so clear. So I colour them red and green, for fail or pass. I can do this with Conditional Formatting, but that still means marking the range by hand then entering Condition 1, Condition 2 for every range in every sheet. It really would be easier if I could just run a macro on the whole file. I just don't know the exact way of doing
for
for c=A to C
for r= 1 to 3

If [cellcr=P then cell_background = green]
if [cellcr=F then cell_background = red]

How exactly do I address the variable CELL_BACKGROUND? In html it might be backgroundcolor=red. But in OO basic?? Where can I read a list of all available variables??
How exactly do I address individual cells? CELLcr??? Or just cr??

[JohnSUN-Pensioner]

Just create two styles - "Red" and "Green."
In the Background tab set the color.
In the Number tab set Format code "F" or "P"
Formula that exhibited before the letter "F" or "P" rewrite so that it assigns the cell the desired style.

For access to the background of the cell use methods:
oSheets = ThisComponent.getSheets() - all sheets of workbook
oSheet = oSheets.getByIndex(0) - first sheet by number
or oSheet = oSheets.getByName("Example") - by name
oCellByPosition = oSheet.getCellByPosition(nColumn, nRow) - one cell
oCellByPosition.CellBackColor = RGB(128, 0 ,255) - background of this cell

[Villeroy]

Macros are not more covenient than the built-in features. The opposite is true, particularly if you can not really program. But even if you are a programming wizard, what are you going to program if you do not know the program's environment perfectly well which is the office suite.

If they make more than 5, a formula puts an F in the neighbouring cell, otherwise P.

=IF(A1>5;"F";"x")&T(STYLE(IF(A1>5;"red_style";"Standard")))
The above formula is nothing but a program written in a functional programming language designed for non-programmers.
It uses relative references so it can be applied to many cells by mere copy&paste without changing any code.
It updates the result and the cell style fully automatically with every change of a precedent (a macro would not).
The first solution with Format>COnditional... would even work when you open the same file in MS Excel (macros are incompatible and Excel does not know the STYLE function)
The built-in features can be used in millions of variations. An amateur macro does one particular thing.

[Pedroski]

Amazing what a bit of reading can do! This does what I want, just needs fiing to perfection:

Code: Select all

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

Sub backcol
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
REM choose which sheet use s
For s = 0 to 2 
Doc = ThisComponent

Sheet = Doc.Sheets(s)
REM choose which row and column use r and c
for r = 0 to 3
for c = 0 to 3

Cell = Sheet.getCellByPosition(c,r)
REM reset cell to white to compensate for changes, insertions, alterations
Cell.CellBackColor = RGB(255,255,255) 
Rem end reset
If Cell.String = "F" then Cell.CellBackColor = RGB(255, 0, 0)
If Cell.String = "P" then Cell.CellBackColor = RGB(0, 255, 0) 

next c
next r
next s
End sub

Not too sure about Doc = ThisComponent Anyone out there can explain it??

[Pedroski]

Filed it a bit, this works for me! Still can't fathom Doc = ThisComponent

Code: Select all

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

Sub backcol
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

REM choose which sheet use s
For s = 0 to 2 
Doc = ThisComponent

Sheet = Doc.Sheets(s)
REM set cell background to transparent
IsCellBackgroundTransparent = 1
REM choose which row and column use r and c
for r = 0 to 3
for c = 0 to 3

Cell = Sheet.getCellByPosition(c,r)

If Cell.String = "F" then Cell.CellBackColor = RGB(255, 0, 0)
If Cell.String = "P" then Cell.CellBackColor = RGB(0, 255, 0) 

next c
next r
next s
End sub

[Charlie Young]

Filed it a bit, this works for me! Still can't fathom Doc = ThisComponent

Code: Select all

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

Sub backcol
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

REM choose which sheet use s
For s = 0 to 2 
Doc = ThisComponent

Sheet = Doc.Sheets(s)
REM set cell background to transparent
IsCellBackgroundTransparent = 1
REM choose which row and column use r and c
for r = 0 to 3
for c = 0 to 3

Cell = Sheet.getCellByPosition(c,r)

If Cell.String = "F" then Cell.CellBackColor = RGB(255, 0, 0)
If Cell.String = "P" then Cell.CellBackColor = RGB(0, 255, 0) 

next c
next r
next s
End sub

ThisComponent is simply a reference to to document running the macro. You could eliminate Doc = ThisComponent and replace all the other references to Doc by ThisComponent and the code would work the same.

See the discussion about The current document in the Basic Programming Guide.

[Pedroski]

Could ThisComponent be replaced with a url or a local file?

Should I have declared s r and c thus: Dim s as Integer Dim r as Integer Dim c as Integer ???

[Charlie Young]

Could ThisComponent be replaced with a url or a local file?

No. It is possible to open and manipulate local files, but that's really a whole new subject. If by a url you mean a web link, that's a different topic yet,

You are perhaps confused the the variable name Doc. There is nothing special about that, it could be Dog or Cat or Whale.

[Pedroski]

Could you just comment on the above, whether I should Dim the loop variables?? It seems to work without declaration.

[Charlie Young]

Could you just comment on the above, whether I should Dim the loop variables?? It seems to work without declaration.

If, before any executable statement (right after REM ***** BASIC ***** in your above), you put the line

Option Explicit

then you must Dim all variables, otherwise it is optional. I almost always use it, unless I either forget or I'm working with someone else's code. It is generally considered a good idea to do such Dimming, especially if you're a beginner, since it prevents certain hard-to-find errors. In the case of your loop variable, if you don't dim it, it will be what is called Variant, instead of the more appropriate Integer or Long, and Variant, being more general, could be less efficient.

[Pedroski]

Thanks very much! One last question: Can I do it like this: Dim c,r,s as Integer or must I declare each separately?

[Charlie Young]

Thanks very much! One last question: Can I do it like this: Dim c,r,s as Integer or must I declare each separately?

You can do each on its own line, or if you do them on one line, you must still specify the type of each individually, like

Code: Select all

Dim c As Integer, r As Integer, s As Integer

[Pedroski]

Thanks a million!