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

Here's a good place to start. Make sure you have the correct cell
active when you run this code.

In a standard module:

Option Explicit

Sub SetCellColors()
Dim vH, vP, i As Long, lColor As Long
vH = ActiveSheet.Range("F7:F10")
vP = ActiveSheet.Range("D7:D10")
For i = LBound(vH) To UBound(vH)
Select Case (vH(i, 1) - vP(i, 1))
Case Is < -2: lColor = 111 'Albatros or better
Case -2: lColor = 232 'Eagle
Case -1: lColor = 193 'Birdie
Case 0: lColor = 174 'Par
Case 1: lColor = 235 'Bogey
Case 2: lColor = 124 'D.Bogey
Case Is 2: lColor = 227 'T.Bogey or worse
End Select
ActiveCell.Offset(1, i + 9).Interior.Color = lColor
Next 'i
End Sub

--
Garry

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

Why not use a worksheet change event?

--

HTH

Bob

"GS" wrote in message ...
Here's a good place to start. Make sure you have the correct cell active
when you run this code.

In a standard module:

Option Explicit

Sub SetCellColors()
Dim vH, vP, i As Long, lColor As Long
vH = ActiveSheet.Range("F7:F10")
vP = ActiveSheet.Range("D7:D10")
For i = LBound(vH) To UBound(vH)
Select Case (vH(i, 1) - vP(i, 1))
Case Is < -2: lColor = 111 'Albatros or better
Case -2: lColor = 232 'Eagle
Case -1: lColor = 193 'Birdie
Case 0: lColor = 174 'Par
Case 1: lColor = 235 'Bogey
Case 2: lColor = 124 'D.Bogey
Case Is 2: lColor = 227 'T.Bogey or worse
End Select
ActiveCell.Offset(1, i + 9).Interior.Color = lColor
Next 'i
End Sub

--
Garry

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

Bob Phillips pretended :
Why not use a worksheet change event?

--

HTH

Bob

"GS" wrote in message ...
Here's a good place to start. Make sure you have the correct cell active
when you run this code.

In a standard module:

Option Explicit

Sub SetCellColors()
Dim vH, vP, i As Long, lColor As Long
vH = ActiveSheet.Range("F7:F10")
vP = ActiveSheet.Range("D7:D10")
For i = LBound(vH) To UBound(vH)
Select Case (vH(i, 1) - vP(i, 1))
Case Is < -2: lColor = 111 'Albatros or better
Case -2: lColor = 232 'Eagle
Case -1: lColor = 193 'Birdie
Case 0: lColor = 174 'Par
Case 1: lColor = 235 'Bogey
Case 2: lColor = 124 'D.Bogey
Case Is 2: lColor = 227 'T.Bogey or worse
End Select
ActiveCell.Offset(1, i + 9).Interior.Color = lColor
Next 'i
End Sub

-- Garry

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



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

That can all be controlled.

--

HTH

Bob

"GS" wrote in message ...
Bob Phillips pretended :
Why not use a worksheet change event?

--

HTH

Bob

"GS" wrote in message
...
Here's a good place to start. Make sure you have the correct cell active
when you run this code.

In a standard module:

Option Explicit

Sub SetCellColors()
Dim vH, vP, i As Long, lColor As Long
vH = ActiveSheet.Range("F7:F10")
vP = ActiveSheet.Range("D7:D10")
For i = LBound(vH) To UBound(vH)
Select Case (vH(i, 1) - vP(i, 1))
Case Is < -2: lColor = 111 'Albatros or better
Case -2: lColor = 232 'Eagle
Case -1: lColor = 193 'Birdie
Case 0: lColor = 174 'Par
Case 1: lColor = 235 'Bogey
Case 2: lColor = 124 'D.Bogey
Case Is 2: lColor = 227 'T.Bogey or worse
End Select
ActiveCell.Offset(1, i + 9).Interior.Color = lColor
Next 'i
End Sub

-- Garry

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



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

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!

--
Garry

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