I am using office 2000 and tring to write a function in which th
argument is multiplied by 12. If the result is less than 36 I woul
like the answer to be highlited in a red cell. If it is greater that o
equal to 36 it will remain a white cell. What I have written gives m
the numerical answer but does not change the color of the box. This i
what I have. Thank you for your help
Mike

Public Function mult(a As Single) As Single
mult = a * 12
If 36 mult Then

ActiveCell.Interior.ColorIndex = 3
Else
ActiveCell.Interior.ColorIndex = 2
End If

End Functio

--
Message posted from http://www.ExcelForum.com

"red5 " wrote in message
...

I am using office 2000 and tring to write a function in which the
argument is multiplied by 12. If the result is less than 36 I would
like the answer to be highlited in a red cell. If it is greater that
or equal to 36 it will remain a white cell. What I have written
gives me the numerical answer but does not change the color
of the box.
This is what I have. Thank you for your help
Mike

Public Function mult(a As Single) As Single
mult = a * 12
If 36 mult Then

ActiveCell.Interior.ColorIndex = 3
Else
ActiveCell.Interior.ColorIndex = 2
End If

End Function


I don't think you can be sure that the activecell is the cell with the
formula in.

The function would be called everytime that cell has to re-calculate,
but the activecell would not move around during that process I think.

Why not just use conditional formatting on the cell outide of VBA?

Alan.

Hi red5

Have a look at Conditional Formatting within Excel help. Extract below.

1. Do one of the following:
To use values in the selected cells as the formatting criteria, click Cell
Value Is, select the comparison phrase, and then type a constant value or a
formula. If you enter a formula, start it with an equal sign (=).

To use a formula as the formatting criteria (to evaluate data or a
condition other than the values in selected cells), click Formula Is and
then enter the formula that evaluates to a logical value of TRUE or FALSE.

2. Click Format.
3. Select the formatting you want to apply when the cell value meets the
condition or the formula returns the value TRUE.
4. To add another condition, click Add, and then repeat steps 1 through
3. You can specify up to three conditions. If none of the specified
conditions are true, the cells keep their existing formats.

--
XL2002
Regards

William



"red5 " wrote in message
...
| I am using office 2000 and tring to write a function in which the
| argument is multiplied by 12. If the result is less than 36 I would
| like the answer to be highlited in a red cell. If it is greater that or
| equal to 36 it will remain a white cell. What I have written gives me
| the numerical answer but does not change the color of the box. This is
| what I have. Thank you for your help
| Mike
|
| Public Function mult(a As Single) As Single
| mult = a * 12
| If 36 mult Then
|
| ActiveCell.Interior.ColorIndex = 3
| Else
| ActiveCell.Interior.ColorIndex = 2
| End If
|
| End Function
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|