Excel 2007 - Using a Custom Function to Set Row Color

[Don]

I have a little function that sets the value of a cell depending on the date
relative to a couple of other dates and a "Yes"/"No" item:

Function CurrentTaskStatus(Milestone As String, _
TaskEndDate As Date, MonthStart As Date, MonthEnd
As Date, _
TaskProj As Integer, TaskComp As Single)


If (TaskEndDate >= MonthStart) And (TaskEndDate <= MonthEnd) And (Milestone
= "Yes") Then
CurrentTaskStatus = TaskComp
Else
CurrentTaskStatus = 0
End If

End Function



What I would like to do is set the color of the row in which the cell is
located to a specific color based on the conditions. I have tried things
like

rows("A45:z45).interior.color = vbBlue

with no success. Generally, a #VALUE! error appears in the cell when I try
anything that might set the color.

Am I trying to do the impossible? Or am I just way off course.

Any suggestions will be greatly appreciated!

Thanks!

Don

 

[Gord Dibben]

Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP

 

[Don]

Gord,

My first impressions of Conditional Formatting were that it is more of a
global condition. I am looking now reading through more examples to see if
I either don't fully understand the concept or I can adapt it to my needs.

Thanks!

Don

 

[Gord Dibben]

CF can be used on one cell or a range of cells.

Simple example................

Select A45:Z45 and CF>Formula is: =$A$45="condition" where "condition" is
your desired criterion.

Format to Blue Pattern.

When A45 meets the condition, A45:Z45 will be colored blue.

Note the $ signs to fix A45 as the trigger cell.


Gord

 

[Don]

Gord,

Do you know of any VBA examples? If so, would you post the link(s)?

Thanks!

Don

CF can be used on one cell or a range of cells.

Simple example................

Select A45:Z45 and CF>Formula is: =$A$45="condition" where "condition" is
your desired criterion.

Format to Blue Pattern.

When A45 meets the condition, A45:Z45 will be colored blue.

Note the $ signs to fix A45 as the trigger cell.


Gord

Gord,

My first impressions of Conditional Formatting were that it is more of a
global condition. I am looking now reading through more examples to see if
I either don't fully understand the concept or I can adapt it to my needs.

Thanks!

Don

Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP