Function in a VBA prog. or something like that

[Swingleft]

Hi there,

below there is a part of a score scar for a golf complex

in a prog i have these lines ( partial of the prog)

H1 = Sheets("Invoer").Range("F7").Value
H2 = Sheets("Invoer").Range("F8").Value
H3 = Sheets("Invoer").Range("F9").Value
H4 = Sheets("Invoer").Range("F10").Value

P1 = Sheets("Invoer").Range("D7").Value
P2 = Sheets("Invoer").Range("D8").Value
P3 = Sheets("Invoer").Range("D9").Value
P4 = Sheets("Invoer").Range("D10").Value

According to the calculation of H1 - P1

the case below should set a color.no.

Select Case Colornr
Case Is < -2 'Albatros or Better
Color = 111
Case Is -2 'Eagle
Color = 232
Case Is -1 'Birdie
Color = 193
Case Is 0 'Par
Color = 174
Case Is 1 'Bogey
Color = 235
Case Is 2 'D. Bogey
Color = 124
Case Is > 2 'T. Bogey or Worse
Color = 227
End Select

is there an easy way to 'set color to the cell

i think it has something to do with a function but i do not know how

this is where the color needs to be set to

ActiveCell.Offset(1, 10).Value = H1
ActiveCell.Offset(1, 11).Value = H2
ActiveCell.Offset(1, 12).Value = H3
ActiveCell.Offset(1, 13).Value = H4

for example. P1 = 4, H1 = 6

so according to the case above ColorNo = 2
and color = 124

i do hope some body understands my problem.

Thanks for all your help

Swingleft

 

[Bob Phillips]

Why not use a worksheet change event?

 

[GS]

Bob Phillips pretended :

Why not use a worksheet change event?

--

HTH

Bob

That idea will cause the code to fire whenever changes occur. This may
not be what the OP wants since the result affects a cell relative to
the active cell at the time the code runs. Normally, I'd agree this is
te sort of thing I'd use the Change event for, but without any further
info it's hard to determine if that's a viable approach.

 

[Bob Phillips]

That can all be controlled.

--

HTH

Bob

Bob Phillips pretended :

That idea will cause the code to fire whenever changes occur. This may not
be what the OP wants since the result affects a cell relative to the
active cell at the time the code runs. Normally, I'd agree this is te sort
of thing I'd use the Change event for, but without any further info it's
hard to determine if that's a viable approach.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

 

[GS]

Bob Phillips expressed precisely :

That can all be controlled.

--

HTH

Bob

Bob,
I don't disagree. It just seems to me that the OP would prefer to set
the active cell before running the code. I could be wrong about that,
though!