Hi All,

I am newbie with VBA. I need some help. I am trying to write a function that
will do a sum of 2 numbers. Depending on the result of the sum, the result
needs to be color coded i.e 0 - 10 is yellow, 11 - 20 is red, 21 - 30 is
blue. I need to create about 8 ranges. Excel's conditional formatting only
allows 3 criteria. I am able to get the sum bit working but I can't set the
color. Can any help please?

Many thanks in advance.
Regards
Eddie

Hi it's me again,

I have included a sample of the code I wrote:

Function AddTwo(arg1, arg2)

ActiveCell.Font.Color = vbRed
AddTwo = arg1 + arg2

End Function

And I call the function "=AddTwo (A1, B1)"

It returns the right sum but it doesn't change the color.

Please help.

Regards
Eddie

"Eddie" wrote in message
...
Hi All,

I am newbie with VBA. I need some help. I am trying to write a function
that
will do a sum of 2 numbers. Depending on the result of the sum, the result
needs to be color coded i.e 0 - 10 is yellow, 11 - 20 is red, 21 - 30 is
blue. I need to create about 8 ranges. Excel's conditional formatting only
allows 3 criteria. I am able to get the sum bit working but I can't set
the
color. Can any help please?

Many thanks in advance.
Regards
Eddie

hi,
use select case method

Dim Results
Results = x+y
ResultsRange = Range("where you are going to put it.")
Select Case results ' Evaluate results.
Case 0 to 10
Resultsrange.font.colorindex = 6 'yellow
Case 11 to 20
Resultsrange.font.colorindex = 3 'red
Case 21 to 30
Resultsrange.font.colorindex = 5 ' blue
Case Else ' Other values.
msgbox(" value out of range")
End Select
in a blank workbook put and run this code for vb color
index numbers.
Sub macGetColors()
' Macro written 2/10/00 by FSt1

Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop
Msgbox("Macro Complete.")
End Sub

-----Original Message-----
Hi All,

I am newbie with VBA. I need some help. I am trying to
write a function that
will do a sum of 2 numbers. Depending on the result of
the sum, the result
needs to be color coded i.e 0 - 10 is yellow, 11 - 20 is
red, 21 - 30 is
blue. I need to create about 8 ranges. Excel's
conditional formatting only
allows 3 criteria. I am able to get the sum bit working
but I can't set the
color. Can any help please?

Many thanks in advance.
Regards
Eddie


.

Hiya

Thanks for the piece of code. Unfortunately for me, I am still stuck. How do
I use this Case structure? What I have done is paste your code into my
AddTwo (Arg1, Arg2) function. It still doesn't change color. I think I may
have use the Range command wrongly. What and how do I pass in the range into
the Range command? Do I need to pass in as a parameter? Do I need to declare
ResultsRange? Please help. I am as confused as ever.

Many thanks in advance.

Regards
Eddie

wrote in message
...
hi,
use select case method

Dim Results
Results = x+y
ResultsRange = Range("where you are going to put it.")
Select Case results ' Evaluate results.
Case 0 to 10
Resultsrange.font.colorindex = 6 'yellow
Case 11 to 20
Resultsrange.font.colorindex = 3 'red
Case 21 to 30
Resultsrange.font.colorindex = 5 ' blue
Case Else ' Other values.
msgbox(" value out of range")
End Select
in a blank workbook put and run this code for vb color
index numbers.
Sub macGetColors()
' Macro written 2/10/00 by FSt1

Sheets("Sheet1").Select
Range("B2").Select
Set ci = Range("A1")
ci.Value = 1
Set c = Range("B2")
Do Until ci 56
Set c2 = c.Offset(1, 0)
Set cnum = c.Offset(0, 1)
c.Interior.ColorIndex = ci.Value
c.Offset(0, 1) = ci.Value
ci.Value = ci.Value + 1
Set c = c2
c.Select
Loop
Msgbox("Macro Complete.")
End Sub

-----Original Message-----
Hi All,

I am newbie with VBA. I need some help. I am trying to
write a function that
will do a sum of 2 numbers. Depending on the result of
the sum, the result
needs to be color coded i.e 0 - 10 is yellow, 11 - 20 is
red, 21 - 30 is
blue. I need to create about 8 ranges. Excel's
conditional formatting only
allows 3 criteria. I am able to get the sum bit working
but I can't set the
color. Can any help please?

Many thanks in advance.
Regards
Eddie


.