Simple Currency Conditional Formatting

[unmec]

Hello,
I currently have a purchase order database that lists all of the general PO information to keep a log as we assign numbers.

I have one column for all of the amounts of the POs and I am hoping to add a few conditional formatting conditions to color the cell if it is in a different currency.

No Format for USD
Yellow for Pounds
Green for Euros

So far I have just been changing the currency format to show the different types, but when I am looking them up, I can't always quickly see a different symbol and a different color would help. I just don't want to have to fill in the color every time I change the format as well.

Thanks for your help!

 

[venkat1926]

first I suppose you enter the numbers only and then format it as various currency formats
in that case there are many ways of solving your requirement including a conditional formatting by using the character map of
the currency code. for e.g. char of $ is 36, for euro 128 and for uk pound 163
but there is no built in formula to get the currency format of a cell. however the follwing webpag
http://www.mvps.org/dmcritchie/excel/formula.htm
gives a function

Function GetFormat(Cell as Range) as String
   GetFormat = cell.NumberFormat
End Function

this has to be copied in the vbeditor
then getformat can be used as formula like any other formula function like sum, count etc.
now you pre-select the cells which you have already formatted as different currencies.
click format-conditionalforamtting

under condition 1
choose
the formula is
on the right side copy this formula
=SEARCH(CHAR(128),getformat(A1))>0
clcik format in the conditional formatting window and choose pattern(last item in the top)
and choose green color

click add
under condition 2 also
the formula is
on right side type this formula
=SEARCH(CHAR(163),getformat(A1))>0
and choose pattern as yellow color
click ok


in case new cells are to be added copy the cells where the conditonal formatting has
been already done and down pastespecial-format
and THEN only do currency formats.

hope this will help you.

I personally find a macro will be easier to operate once the macro is created.