Excel 2007 - Using a Custom Function to Set Row Color
 

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

Excel 2007 - Using a Custom Function to Set Row Color
 

Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP

On Sat, 21 Feb 2009 17:25:40 -0500, "Don" wrote:

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

Excel 2007 - Using a Custom Function to Set Row Color
 

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" <gorddibbATshawDOTca wrote in message
...
Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP

On Sat, 21 Feb 2009 17:25:40 -0500, "Don" wrote:

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

Excel 2007 - Using a Custom Function to Set Row Color
 

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

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

Select A45:Z45 and CFFormula 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

On Sun, 22 Feb 2009 11:50:32 -0500, "Don" wrote:



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" <gorddibbATshawDOTca wrote in message
.. .
Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP

On Sat, 21 Feb 2009 17:25:40 -0500, "Don" wrote:

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

Excel 2007 - Using a Custom Function to Set Row Color
 

Gord,

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

Thanks!

Don



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
CF can be used on one cell or a range of cells.

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

Select A45:Z45 and CFFormula 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

On Sun, 22 Feb 2009 11:50:32 -0500, "Don" wrote:



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" <gorddibbATshawDOTca wrote in message
.. .
Functions return values.

They don't format cells.

Conditional Formatting is used for that.


Gord Dibben MS Excel MVP

On Sat, 21 Feb 2009 17:25:40 -0500, "Don"
wrote:

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