Help with function/cond. formatting

R

Roz

Hey all you smarties out there! Here is what I have: Column E with
these two formats: =AND(EvalCell(E3)<D3,TODAY()>G3), and
=AND(E3<D3,TODAY()>G3). I created a UDF "EvalCell" to be able to
calculate if I have 3+1 in a cell, representing that I received 3 items
in one shipment, and 1 more item in a separate shipment. In column D, I
might have the same 3+1. I need the computer to recognize that this is
4, and that columns D and E are equal. right now, if they aren't
equal, ie E, the received column is less than D the ordered column, E
turns red to alert me that something was not delivered. Column E and
it's formulas are fine and they work, but I need to format Column D so
that it recognizes 3+1=4, and subsequently Column E recognizes that
they are both equal. Can anyone help? Thanks a bunch.
 
B

Bob Phillips

Why not use

=AND(evalcell(E3)<evalcell(D3),TODAY()>G3)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Roz

Thanks Bob, It kind of works...but both cells that this function/formula
applies to only works if the numbers are the same...ie 3+3 in D and 3+3
in E. My problem is that while I may have orderd 3 items, then ordered
3 more, I may receive them in say 4 items in the first shipment, and 2
items in the second. Excel will look at 3+3 in D and 4+2 in E and
still recognize E as being less than D and turn it red, even though
they are equal. I need this formula to recognize any combination of
numbers as long as they are equal. If not equal then E turns red. Any
thoughts?
 
B

Bob Phillips

Roz,

It depends upon how your EvalCell is written. I knocked up a quickie, and
thet returned equal for 3+3 and 4+2.

What does your function look like?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Roz

Well, I created the new function like you said, and copied letter fo
letter, space for space what you wrote. Then I added in the pertainin
cells the new conditional format with the eval cell. I then had to ad
another condition with the original formula =AND(E3<D3,TODAY()>G3)
because if I only had the one condition with the evalcell, it woul
only turn the cell red if there was a 3+1 type of formula in it, rathe
than a single digit. I need it to do both. It wonderfully works now!
It works if E is less than D, if there is 3+1 in D and 3+1 in E, o
even if ther is 4 in D and 3+1 in E. The only time it doesn't work i
if I have 3+1 in D, and 4 in E it does not recognize this as the same.
I tried to put =EvalCell(D3) only in column D, but that doesn't do it.
Am I missing something
 
B

Bob Phillips

Show us the EvalCell code.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Roz

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = (RefCell)
End Function

It will do the 3+3 in D, and 4+2 in E, and come up equal. But my issue
now is that if I have 2+2 in D and 4 in E, it doesn't recognize that as
being equal, or any other combo of numbers that equal 4. I tried to put
in an eval cell in D, but I must have done it wrong. Any thoughts?
 
B

Bob Phillips

Use

Function EvalCell(RefCell As Range)
EvalCell = Evaluate(RefCell.Value)
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
G

Gord Dibben

Bob

The function I posted works for me. Why the Evaluate added in your version?

Function EvalCell(RefCell As String)
'Application.Volatile
EvalCell = (RefCell)
End Function

D3 contains (6+3+3)/4

=EvalCell(D3) returns 3


Gord
 
B

Bob Phillips

Strange Gord, because it doesn't for me. It returns exactly the same string.
I have to evaluate it to get a value.

Bob
 
R

Roz

Well Bob and Gord, I tried to use the new function, and nothing happens,
I played with it all sorts of ways to get it to work, but nothing shows
up different. I still have a cell E3 with 4, and D3 with 3+1 and it
doesn't recognize this as equal. If it were switched as in D3 with 4
and E3 with 3+1 it works fine, no adjustments needed. Do I just need
to put a formula in D?
 
B

Bob Phillips

Roz,

Why don't you post me a workbook, we must be missing something.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
P

pinmaster

I also tried those UDF with no results

In Visual Basic Editor.......Insert/Module....then pasted your codes.

if I put 3+3 in A1 and =EvalCell(A1) in B1 all I get is #NAME? error.

Excel 2000
Win 98SE

JG
 
R

Roz

I got the same results. With the old code:

Function EvalCell(RefCell as String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

If I just put that in, it will give me the NAME Error. But if you pu
in the cell/column conditional formatting with the formula
=AND(EvalCell(E3)<EvalCell(G3),TODAY()>G3) for condition 1, then
=AND(E3<D3,TODAY()>G3) for condition 2, everything works: the numbe
in E3 turns red if E3 is less than D3, and only if the date in G3 i
past today's date. The evalCell function allows the cell to calculat
a formula that I put in a cell, for ex. 3+1, the cell will realize thi
is equal to 4, and if 4 is in D3 and 3+1 is in E3, everything work
fine. What I need it to do is in D3, be able to recognize if I orde
items say I order 1+3 items (I ordered one, then realized I needed
more, so I ordered 3 more) in D3, but in E3 it might say 3+1, meanin
that I received 3 items in one shipment, and one in another. Exce
won't recognize this as being equal and it will turn the 3+1 in E3 red
I need excel to see that 1+3 in one cell and 3+1 in another cell ar
equal, and not turn the 3+1 red. What do you think
 
G

Gord Dibben

JG

I don't know what to say about that other than "are you sure you copied/pasted
correctly into a General Module in the workbook"?

Either UDF works for me.


Gord
 
G

Gord Dibben

Roz

Looks like a typo in your condition 1 formula.

Should maybe read =AND(EvalCell(E3)<EvalCell(D3),TODAY()>G3)

I select F3 and CF.

Stick the formula is: into the dialog box.

Enter December 20 2005 in G3 3+1 in E3 1+4 in D3 and F3 turns color because
both arguments are met.

Change D3 to 1+3 and F3 goes to default because both arguments have not been
met.

CF also recognizes a single number in D3. Enter 4 in D3 and no color in F3.

Enter 5 in D3 and F3 turns color.

Where do you have the UDF located?

It must be in a General module in the workbook.


Gord Dibben Excel MVP
 
P

pinmaster

Ok found the problem, for me at least, the module was inserted under th
VBAProject(PERSONAL.XLS), when I put the module in the current workboo
it worked perfectly.



Regards
J
 
G

Gord Dibben

pin

You can still use the UDF from your Personal.xls.

You must preface the Function as such....

=Personal.xls!EvalCell(A1)

Gets a little unwieldy when typing into a CF dialog box.


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with if statement 4
Complex cond. formatting...Pls. Help! 1
COPYING CONDITIONAL FORMATTING WHEN COPYING CELLS OVER 2
Conditional formatting with time 4
Please suggest any formulae / functions 2
Trouble w/ conditional formatting and dates 5
Pivot Table/Conditional Formatting Guru Help 2
Excel Help with dates 2

Top