jschleifer
Newbie
Joined: 08 Feb 2009
Posts: 1
|
 Posted: Sun Feb 08, 2009 11:02 am Post subject: oocalc using a function to affect cell style and value |
 |
|
Hey folks,
I've been searching up and down the web to try and find an answer for this, but after days of searching haven't found anything that answers my question yet. I'm hoping that a quick post here will result in someone pointing me in the right direction.
I'm creating a spreadsheet to help track the progress of various projects. I want to have a "current status" column that will search through the daily columns on each row and find the latest status.
For example, let's say I have project X. My cell layout would look something like this:
A1: Project X
B1: Status
C1: L
D1:
E1:
F1: p1
G1:
H1:
I1: p2
J1:
and so on.
in B1 I have a formula like this:
=CheckValues(C1:J1)
CheckValues is a function that looks like this:
| Code: |
Function CheckValues ( s As Range )
Dim Result
Dim x,y
Result = ""
for x=LBound(s,1) to UBound(s,1)
for y=LBound(s,2) to UBound(s,2)
Select Case s(x,y)
Case "l","L"
Result = "Launch"
Case "p1","P1"
Result = "Phase 1"
Case "p2","P2"
Result = "Phase 2"
Case "p3","P3"
Result = "Phase 3"
End Select
next y
next x
CheckValues = Result
End Function
|
The status would update based on the latest "status" when I check the values. This all works fine and is giving me the results I need. What I would like to do is also change the color of the Status cell based on the results. Unfortunately I only have 3 conditional formats available (using oocalc 2.. it's all I have access to).
Does anyone know of a way to change the cell style in a function?
if that's not possible, can someone point me to some documentation on creating a macro that will update whenever the cell is changed so I don't have to have the users run the macro by hand?
Sorry if this has been covered elsewhere. If it has, I've missed it & would be happy with a slap upside the head and a pointer to the correct thread. 
cheers!
-jason
_________________
-------
Jason Schleifer
http://jasonschleifer.com |
|
