All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

No. Formulas in the worksheet don't color cells.

--
Regards,
Tom Ogilvy

"Floyd" wrote in message
oups.com...
All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

Thank you.

Would this fall under your original statement as well?

Thanks in advance.

Option Explicit

Sub Macro1(Rng1)
Rng1.Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub

Function Test(Rng1 As Range)
If Rng1.Value = 1 Then
Call Macro1(Rng1)
End If
End Function

Yes that would fall under my original statement. Go ahead and try it.

But even if it did work (but lets be clear, it doesn't), didn't you rule out
macros?

--
Regards,
Tom Ogilvy


"Floyd" wrote in message
oups.com...
Would this fall under your original statement as well?

Thanks in advance.

Option Explicit

Sub Macro1(Rng1)
Rng1.Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
End Sub

Function Test(Rng1 As Range)
If Rng1.Value = 1 Then
Call Macro1(Rng1)
End If
End Function

Mr. Ogilvy,

I ruled out macros, since I would not be able to pass multiple
references. The function allows me to pass different references.

Anyway thanks for your assistance.

Cheers.

you can check this out
http://www.ozgrid.com/Services/excel...nal-format.htm

also, in the next version of excel:
Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

--


Gary


"Floyd" wrote in message
oups.com...
All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

Gary,

It would seem useful to alert the OP that you are directing him/her to a
commercial site where he/she can purchase a product to assist their need.
Generally this forum is used to provide programming assistance and not tout
commercial products. It would have been most appropriate if you had said,
I highly recommend this product which can be purchased at so and so. Most
URL's posted here are to informational pages.

Just my opinion of course. You are certainly free to post anything you
want.

--
Regards,
Tom Ogilvy



"Gary Keramidas" wrote in message
...
you can check this out
http://www.ozgrid.com/Services/excel...nal-format.htm

also, in the next version of excel:
Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

--


Gary


"Floyd" wrote in message
oups.com...
All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

For the FREE CFPlus(up to 30 CF's)add-in see Bob Phillips' site.

http://www.xldynamic.com/source/xld.....Download.html


Gord Dibben Excel MVP

On Thu, 29 Sep 2005 16:59:11 -0400, "Tom Ogilvy" wrote:

Gary,

It would seem useful to alert the OP that you are directing him/her to a
commercial site where he/she can purchase a product to assist their need.
Generally this forum is used to provide programming assistance and not tout
commercial products. It would have been most appropriate if you had said,
I highly recommend this product which can be purchased at so and so. Most
URL's posted here are to informational pages.

Just my opinion of course. You are certainly free to post anything you
want.

sorry, i'll remember that next time

--


Gary


"Tom Ogilvy" wrote in message
...
Gary,

It would seem useful to alert the OP that you are directing him/her to a
commercial site where he/she can purchase a product to assist their need.
Generally this forum is used to provide programming assistance and not
tout
commercial products. It would have been most appropriate if you had
said,
I highly recommend this product which can be purchased at so and so. Most
URL's posted here are to informational pages.

Just my opinion of course. You are certainly free to post anything you
want.

--
Regards,
Tom Ogilvy



"Gary Keramidas" wrote in message
...
you can check this out
http://www.ozgrid.com/Services/excel...nal-format.htm

also, in the next version of excel:
Number of conditional format conditions on a cell
Old Limit: 3 conditions
New Limit: Limited by available memory

--


Gary


"Floyd" wrote in message
oups.com...
All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

On 29 Sep 2005 11:03:17 -0700, "Floyd" wrote:

All:

I am attempting to use either a user-defined function in VBA or a
spreadsheet function to conditonally shade a cell.

I would use Excel's conditional formatting; however, it is limited to
three conditions.

Also, I can do this with VBA. Unfortunately, then I would have to
re-refernce the subroutine when the data is moved. Given the dynamics
of this spreadsheet, I would be required to do this often.

Is there anyway to use something like this,IF(A1=1, ColorIndex=1, "") ,
to conditionally format a cell?

Thanks in advance.

Floyd

A formula cannot change the formatting of a cell. This rule is true both for
worksheet functions, and for VBA UDF's, even if they reference a macro.

If a cell is copy/pasted, the formatting would move with it, so I guess I don't
understand your objection to a VBA Sub.


--ron

Thanks for all of the feedback.

My objection to using a VBA routine is this.

Let's say that I have data in D5:AA11. I would have a routine to read
a set of conditions that change with user input in D4:AA4. There are
sets of conditions in this range. Based on the conditions I am
creating a Gant chart by shading cells. So far there should not be any
problem with use a VBA subroutine.

I need to create 10 of these charts on at least 5 different worksheets.
If I had used a function, then I would have more flexibility than
using a subroutine.

Now I copy the cells above and paste in D20. I need to create a second
Gant chart. However, I now need to change the ranges that the
subroutine uses to determine whether conditions are met for the second
chart.

I realize that the subroutine option is viable. Right or wrong, it
seems to me that a function would be easier to implement.

I apologize for not being more explicit earlier.

Cheers.

On 29 Sep 2005 17:08:07 -0700, "Floyd" wrote:

Thanks for all of the feedback.

My objection to using a VBA routine is this.

Let's say that I have data in D5:AA11. I would have a routine to read
a set of conditions that change with user input in D4:AA4. There are
sets of conditions in this range. Based on the conditions I am
creating a Gant chart by shading cells. So far there should not be any
problem with use a VBA subroutine.

I need to create 10 of these charts on at least 5 different worksheets.
If I had used a function, then I would have more flexibility than
using a subroutine.

Now I copy the cells above and paste in D20. I need to create a second
Gant chart. However, I now need to change the ranges that the
subroutine uses to determine whether conditions are met for the second
chart.

I realize that the subroutine option is viable. Right or wrong, it
seems to me that a function would be easier to implement.

I apologize for not being more explicit earlier.

Cheers.

If the conditional formatting on each worksheet is consistent within that
sheet, you could use an event triggered Sub tied to the particular worksheet.
Then, when data is changed (or entered) on that worksheet, the conditional
formatting for that sheet would be applied.
--ron

On 29 Sep 2005 17:08:07 -0700, "Floyd" wrote:

Thanks for all of the feedback.

My objection to using a VBA routine is this.

Let's say that I have data in D5:AA11. I would have a routine to read
a set of conditions that change with user input in D4:AA4. There are
sets of conditions in this range. Based on the conditions I am
creating a Gant chart by shading cells. So far there should not be any
problem with use a VBA subroutine.

I need to create 10 of these charts on at least 5 different worksheets.
If I had used a function, then I would have more flexibility than
using a subroutine.

Now I copy the cells above and paste in D20. I need to create a second
Gant chart. However, I now need to change the ranges that the
subroutine uses to determine whether conditions are met for the second
chart.

I realize that the subroutine option is viable. Right or wrong, it
seems to me that a function would be easier to implement.

I apologize for not being more explicit earlier.

Cheers.

If the conditional formatting on each worksheet is consistent within that
sheet, you could use an event triggered Sub tied to the particular worksheet.
Then, when data is changed (or entered) on that worksheet, the conditional
formatting for that sheet would be applied.
--ron