Custom function - Help please
 

Hello,

I created this function but I don't know why it doesn't work properly.
Any help appreciate.
Thank you.

Regards,
Thierry

Function Tps(Livrele As Range, Requispour As Range)
Dim Delay As Integer
Delay = Range(Requispour).Value - Range(Livrele).Value
If Delay < 0 Then
Range(Requispour.Column + 1).Interior.ColorIndex = 55
Range(Requispour.Column + 1).Font.ColorIndex = 0
Else
Range(Requispour.Column + 1).Interior.ColorIndex = 0
Range(Requispour.Column + 1).Font.ColorIndex = 1
End If
End Function

Custom function - Help please
 

Hello mrjaune,

Change your code to this...


Code:
--------------------
Function Tps(Livrele As Range, Requispour As Range)

Dim Delay As Integer
Delay = Range(Requispour).Value - Range(Livrele).Value
If Delay < 0 Then
Requispour.Offset(0, 1).Interior.ColorIndex = 55
Requispour..Offset(0, 1).Font.ColorIndex = 0
Else
Requispour.Offset(0, 1).Interior.ColorIndex = 0
Requispour.Offset(0, 1).Font.ColorIndex = 1
End If

End Function

--------------------

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=483356

Custom function - Help please
 

you didn't specify the row reference.
Range(Requispour.Column + 1).Interior.ColorIndex = 55

try:
Requispour.Offset(0,1).Interior.ColorIndex = 55

" wrote:

Hello,

I created this function but I don't know why it doesn't work properly.
Any help appreciate.
Thank you.

Regards,
Thierry

Function Tps(Livrele As Range, Requispour As Range)
Dim Delay As Integer
Delay = Range(Requispour).Value - Range(Livrele).Value
If Delay < 0 Then
Range(Requispour.Column + 1).Interior.ColorIndex = 55
Range(Requispour.Column + 1).Font.ColorIndex = 0
Else
Range(Requispour.Column + 1).Interior.ColorIndex = 0
Range(Requispour.Column + 1).Font.ColorIndex = 1
End If
End Function

Custom function - Help please
 

Hi Thierry,

Firstly, change:

Delay = Range(Requispour).Value - Range(Livrele).Value
to:
Delay = Requispour.Value - Livrele.Value

You have already declared Requispour and Livrele as ranges.

It is not clear to me what range you are attempting to define in the
expression:

Range(Requispour.Column + 1)

Perhaps you could describe the range in words?

It should also be noted that, if you intend to use your function as a
worksheet function (UDF), the UDF cannot change the format of cells other
than the calling cell.

---
Regards,
Norman



wrote in message
oups.com...
Hello,

I created this function but I don't know why it doesn't work properly.
Any help appreciate.
Thank you.

Regards,
Thierry

Function Tps(Livrele As Range, Requispour As Range)
Dim Delay As Integer
Delay = Range(Requispour).Value - Range(Livrele).Value
If Delay < 0 Then
Range(Requispour.Column + 1).Interior.ColorIndex = 55
Range(Requispour.Column + 1).Font.ColorIndex = 0
Else
Range(Requispour.Column + 1).Interior.ColorIndex = 0
Range(Requispour.Column + 1).Font.ColorIndex = 1
End If
End Function

Custom function - Help please
 

Hi Norman,
And thanks for your help.

I want do for example:
Delay = cell H11 - cell F11
and if the result is negative (the result is in cell J11)
I want the cell J11 fill in orange and the result in white.
Otherwise, if the result is positive I want no fill and text in black.
And next I can copy my Tps function to a J12:J650 range.

I can't use a conditional formating because I can't copy the formula to
a range.
I tried it before.

In my mind I want write this formula in cell J11: =Tps(F11,H11)

Regards,
Thierry

Custom function - Help please
 

Hi Thierry,

I can't use a conditional formating because I can't copy the formula to
a range.
I tried it before.

Select cells J11:J650
Format | Conditional Formatting
Formula Is =$H11-$F11<0 Fill color: Orange, Font color: White

worked for me.

---
Regards,
Norman



wrote in message
ups.com...
Hi Norman,
And thanks for your help.

I want do for example:
Delay = cell H11 - cell F11
and if the result is negative (the result is in cell J11)
I want the cell J11 fill in orange and the result in white.
Otherwise, if the result is positive I want no fill and text in black.
And next I can copy my Tps function to a J12:J650 range.

I can't use a conditional formating because I can't copy the formula to
a range.
I tried it before.

In my mind I want write this formula in cell J11: =Tps(F11,H11)

Regards,
Thierry