[SOLVED] Audit function needed

In a multi-currency environment my staff keep mixing up currencies in
their spreadsheets e.g. subtracting costs in dollars from revenues in Euros.

I would like to develop a macro that examines formulas in a spreadsheet
and looks for cells formatted in different currencies.

Some pointers on how to get started would be appreciated.

Don't know your formats, so I will use a format of £#,##0.00;(£#,##0.00)

Dim cell As Range
Dim rng As Range

For Each cell In Selection
If Not IsEmpty(cell) Then
If IsNumeric(cell.Value) Then
If cell.NumberFormat <> "£#,##0.00;(£#,##0.00)" Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
End If
End If
Next cell

If Not rng Is Nothing Then
rng.Select
End If

--
HTH

Bob Phillips

"bvm" <[email protected]> wrote in message
news:[email protected]...
> In a multi-currency environment my staff keep mixing up currencies in
> their spreadsheets e.g. subtracting costs in dollars from revenues in
Euros.
>
> I would like to develop a macro that examines formulas in a spreadsheet
> and looks for cells formatted in different currencies.
>
> Some pointers on how to get started would be appreciated.

that would not (easily) happen in a well designed sheet.

make sure that different currencies end up in different columns.
Or:

have 1 column for "values" and a separate column as a currency selector.
(3rd column ofcourse the calculated value in "reporting currency"

never rely on numberformatting... (what if they paste values?)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


bvm wrote :

> In a multi-currency environment my staff keep mixing up currencies in
> their spreadsheets e.g. subtracting costs in dollars from revenues in
> Euros.
>
> I would like to develop a macro that examines formulas in a
> spreadsheet and looks for cells formatted in different currencies.
>
> Some pointers on how to get started would be appreciated.

bvm wrote:
> In a multi-currency environment my staff keep mixing up currencies in
> their spreadsheets e.g. subtracting costs in dollars from revenues in
> Euros.
>
> I would like to develop a macro that examines formulas in a spreadsheet
> and looks for cells formatted in different currencies.
>
> Some pointers on how to get started would be appreciated.
Thanks Bob that helped and by combining your suggestions with the
Precedent function I have been able to do exactly what I wanted as
stated originally.

I appreciate the sentiment in keepITcool - however if everyone drove
their cars properly there would not be any road accidents, but in the
real world....