Conditionally Formatting a Cell

[Floyd]

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

 

[Tom Ogilvy]

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

 

[Floyd]

Thank you.

 

[Floyd]

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

 

[Tom Ogilvy]

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?

 

[Floyd]

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.

 

[Gary Keramidas]

you can check this out
http://www.ozgrid.com/Services/excel-conditional-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

 

[Tom Ogilvy]

Not based on what you said:

Unfortunately, then I would have to

re-refernce the subroutine when the data is moved.

was what you gave as a reason - I interpreted to mean that the code would be
in another workbook.

Apparently you see some difference in VBA, Macros, Subroutine and the
Function you cited which is written in VBA and commonly, like all VBA,
called a macro.

You use much different terminology from most here, so it is difficult to
understand your distinctions.

 

[Tom Ogilvy]

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.

 

[Ron Rosenfeld]

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

 

[Gord Dibben]

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

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


Gord Dibben Excel MVP

 

[Floyd]

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.

 

[Gary Keramidas]

sorry, i'll remember that next time

--


Gary

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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