Setting a cell background color

[SteelAdept]

I am having severe issues getting Excel to automatically set the color
attribute for a cell based on values in the cell. I have tried numerous
times with numerous methods including using the macro recorder to create the
code. However, once I create the code and build my function around it, it
doesn't work! The entire function is below:

Function DecideColor(cTrain As Date, cDoc As Date)
Dim test As String

'This compares the two dates
If cDoc < cTrain Then
ActiveCell.Select
Selection.Interior.ColorIndex = 35
test = "Good"
ElseIf cDoc > cTrain Then
ActiveCell.Select
Selection.Interior.ColorIndex = 3
test = "Bad"
End If

DecideColor = test
End Function

Note, for testing purposes, I am simply puting in dates to ensure they are
different. There are many border situations I will deal with later (such as
what if the two dates are the same, et. al.). When I run this function, it
compares the dates properly, and enters the "Good" or "Bad" appropriately, so
it isn't an issue with the compare statements. It is ONLY that the color
NEVER changes! This is absolutely frustrating. Is there anyone who can help?

 

[JE McGimpsey]

If you are calling this function from the worksheet, then setting the
background color (or any other environment formatting) will not work -
functions called from cells can only return values to their calling cell.

To change the cell color via VBA, you'd need to use an event macro.

However, it seems to me that this could be more easily accomplished
using Conditional Formatting...

 

[JLGWhiz]

The function does what it is supposed to do. It returns the value of "test".
That is all a function is supposed to do. If you want to set the color,
then use Conditional Format or in VBA FormatConditions.

 

[JLGWhiz]

From Vertex42.com website:

Limitations of UDF's:
Cannot "record" an Excel UDF like you can an Excel macro.

More limited than regular VBA macros. UDF's cannot alter the structure or
format of a worksheet or cell.

If you call another function or macro from a UDF, the other macro is under
the same limitations as the UDF.

Cannot place a value in a cell other than the cell (or range) containing the
formula. In other words, UDF's are meant to be used as "formulas", not
necessarily "macros".

Excel user defined functions in VBA are usually much slower than functions
compiled in C++ or FORTRAN.

Often difficult to track errors.

If you create an add-in containing your UDF's, you may forget that you have
used a custom function, making the file less sharable.

Adding user defined functions to your workbook will trigger the "macro" flag
(a security issue: Tools > Macros > Security...).

 

[SteelAdept]

That is what I want it to do, but I want the color change as well. I
thought, from what I read, that the "Selection.Interior.ColorIndex = 3" part
of the code was a Conditional format, but I take it from your post that it is
not. I will look into FormatConditions as you suggest.

Thanks!

 

[SteelAdept]

Thanks but it is only applied to a cell - at least I believe it is. How can
you tell? I have a cell with a formula in it that looks similar to this:
"=DecideColor("8/18/2000","9/14/2008")". This is how I invoke it. Would
that be a worksheet function?

 

[JE McGimpsey]

Yes - if the function is called by entering it in a cell, it can only
return values (that applies to functions called by functions called by
entering in a cell, too, so you can't get away with layering the
functions).