Thanks in adavance for any help provided!!

I am very new to VBA code. I am trying to create a macro for use in a
budget template. My template has accounts across the top and cost
drivers down the side. My general ledger will not accept pennies on
the dollar in budgetary figures. I have used data validation to
prevent this, but users can still paste over the validation. I was
looking for a simple macro that would search the range of entered
figures (from Acct 1 & ABC thru Acct 4 & PQR ... below) and highlight
any cell containing a decimal yellow. Heres the catch, once the error
is corrected, I'd like the cell to return to normal. I was thinking
the best way to do that was once the macro found a cell with a decimal,
it would enter some sort of conditional format into the cell. I was
also hoping there was a way for the macro to indicate in a cell on
another spreadsheet tab if an error was present (ie. the cell turns red
indicating an error or green indicating no error). My information looks
like this...


Acct 1 Acct 2 Acct 3 Acct 4
ABC 1200.00
DEF 1300.00
GHI 1000.00 2000.00
JKL 1500.50
MNO
PQR 1600.00


Budget 2200.00 2800.50 2000.00 1600.00

on my spreadsheet the amount under Acct 1 & ABC oocupies cell B2.

If anymore info is needed, I will gladly provide it. Thanks again for
any help.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Hello Celt,

Why don't you try conditional formatting with a formula like :
=INT(B2)/B2<1
and your selection of format to see your decimals

HTH
Cheers
Carim

Thanks Carim.

However, people are going to have the ability to paste data into this
template and that will wipe out any conditional formating I already
have in place. That's why I was hoping someone could help me come up
with a macro that would use dynamic ranges to check all the data
entered and identify errors as found.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Hello Celt,

A macro solution is always possible ...
However, one has to fully understand your constraints...
An important process is the one that extracts data from the Budget to
go to the General Ledger, how is it done today ?

Carim

Hello Celt,

In the meantime, the following macro will color in red cells which have
decimals ...

Sub MacroRed()
Dim i As Long
Dim j As Long
Dim Lrow As Long
Dim Lcol As Long

Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
Lcol = ActiveCell.Column
For i = 2 To Lrow
For j = 2 To Lcol
Application.Goto Cells(i, j)
If Int(ActiveCell.Value) / ActiveCell.Value < 1 Then
ActiveCell.Font.ColorIndex = 3
End If
Next j
Next i
Range("A1").Select
End Sub


HTH
Cheers
Carim

Thank you Carim!!

Getting my budget into my general ledger is a 2 part process. I use
contactenate formula to pull the required pieces of the informatio
into the "upload" format. Then a general ledger upload process pull
the info in. It won't take pennies on the dollar when uploading, so
was trying to find a way to prevent my staff from entering decimals.
have used Data Validation, but they can just copy and paste over i
nullifying the Validation. So I was looking for a macro that woul
search the "input range" and turn any cell with a decimal yellow.
However, once they fix the error I would like the cell to go back o
normal...that's why i was toying with a macro that somehow use
conditional formatting.

Thank you for this macro!! I am going to play around with it and se
what I can get it to do

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=51879