Named range using conditional formatting

D

derekvho

Hi,
I have a named cell called Econvert which contains the conversion rate
from Dollars to Euros. I am creating a template for others to use and
I need to add a conditional format for when users forget to divide
their inputs by the Econvert.

For example, if they enter a salary number as $40,000 I want them to
divide this number by Econvert so that the end result is
=$40,000/Econvert. I would like the cell to become red if they forget
to divide by Econvert which is why I think that conditional formatting
would help.

Any ideas? Thanks in advance.
 
B

Bob Phillips

You could create a UDF and use that in the CF

Function FormulaOK(rng As Range)
If rng.Count > 1 Then
FormulaOK = CVErr(xlErrRef)
Else
If rng.HasFormula Then
If InStr(LCase(rng.Formula), "econvert") > 0 Then
FormulaOK = True
End If
End If
End If
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bill Ridgeway

Currency conversion rates change by the minute. Including a currency
converter in a spreadsheet more-or-less forces conversions at today's rates
until you next change it. This may, indeed, be what you want for some
applications but can be disadvantageous in the commercial sense over,
perhaps, quite a short period. If this may be the case, I suggest you look
at a more dynamic solution.

For the now though, the answer to your query would be something like -

=IF(OR(A1=0,A1="",B1=0,B1=""),"Error",A1/B1

If either cell has a zero or is blank it returns the ERROR message but if
all is OK does the calculation. You will have to adapt this to suite your
application.

Regards.

Bill Ridgeway
Computer Solutions
 
D

derekvho

Thanks for the suggestion but it's a little complicated for me. I was
just hoping to type in the "Formula is" field in the conditional
formatting box.

I was looking to use something like the ISNUM function but for Named
cell ranges. Does that exist? I'm not sure what a UDF is. User
Defined Function? Would I copy the formula you provided to code in a
macro or would I put this code in a condition using the "Formula is"
option? I'm not very technical so if you could "dumb this down", I'd
really appreciate it.
 
B

Bob Phillips

Just go into the VBIDE (Alt-F11), insert a code module (Insert>Module) and
paste that code.

Then in CF, change Condition 1 to formula Is and add a formula of say
=FormulaOK(A1) where A1 is the cell to check.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

derekvho

Hi Bill, I understand the dynamic nature of the conversion rate but I
would like the flexibility to set the rate as often and to whatever I
would like. Thank you for the suggestion. I don't understand how the
function would help me make sure that the user is using the Econvert
because I need to make sure that whatever value that the user is
inputtiing, is being divided by the Econvert. I have to do this in
multiple cells because the user has to enter a lot of financials.

Please clarify. Thanks again.
 
D

derekvho

Thanks! The formula seems to have the opposite effect. I wanted the
cell to turn red when the user didn't use the Econvert (with the code
and instruction provided, the cell turns red when the user does not use
the Econvert). Any suggestions? Also, I need to apply the conditional
formatting to a lot of cells so going in to each one would really be
time consuming. Any suggestions?
 
D

derekvho

Thanks! The formula seems to have the opposite effect. I wanted the
cell to turn red when the user didn't use the Econvert (with the code
and instruction provided, the cell turns red when the user DOES use
the Econvert). Any suggestions? Also, I need to apply the conditional
formatting to a lot of cells so going in to each one would really be
time consuming. Any suggestions?
 
B

Bill Ridgeway

You wrote << I don't understand how the function would help me make sure
that the user is using the Econvert>>

It helps to make sure the user is using the correct cell reference by
throwing up the ERROR message when it is not used.

Regards.

Bill Ridgeway
Computer Solutions
 
B

Bob Phillips

Just use a formula of

=NOT(FormulaOK(A1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

derekvho

Thanks Bob!
That works. I was wondering if there was a way to have a message box
appear that would not allow the user to continue if the cell unless
they used the Econvert cell. Perhaps the dialogue would say, "You
must divide by Econvert when entering data to ensure correct currency
conversion." Would I put this in the VB code or does conditional
formatting allow for message boxes like a data validation would?
 
D

derekvho

1. For cells that do not have values, I want the cell to be blank
(currently with the present code, it is red since the cell does not
have Econvert in it. What code would I put in for that to happen?
2. Is there a way for a message box to appear as well that doesn't
allow you to continue unless the cell uses Econvert?
 
D

derekvho

I chose Bob's way.

1. For cells that do not have values, I want the cell to be blank
(currently with the present code, it is red since the cell does not
have Econvert in it. What code would I put in for that to happen?
2. Is there a way for a message box to appear as well that doesn't
allow you to continue unless the cell uses Econvert?

help?
 
Top