Jemmeyson wrote:

I am struggling with VBA code to perform the following function
(admittedly I am new to VBA).

I have 3 cells where users can change the value and the 4th cell is
computed with a result like this:

CELL A1 - user input any value from 1 to 5
CELL A2 - user input any value from 1 to 5
CELL A3- user input any value from 1 to 5

CELL A4 computes =(A1+A2+A3)/3 and places the result there.

Just out of curiosity, why not just =AVERAGE(A1:A3) ?

Once per month the user makes changes to any one or three cells
(A1;A2:A3) and A4 computes the result.

At times the user may make no changes at all to these three cells, or
perhaps he/she will change only one cell or two or even three cells
values. Either way the result in A4 needs to be captures as "Previous
Value or Old Value"

Heres where I need VBA help. I want Cell A5 to store the last or old
value of A4 and in A6 I will use conditional formatting to display
either up, down or no change arrows by looking at the New Value in A4
and the Old Value in A5

Please can you help me on this? In anticipation.

The short answer: See the Worksheet_Change event.

The long answer: ...actually, I'll just link to my old logging code. I let
my site lapse so you get a Google Gropes link instead. Shrug.

https://tinyurl.com/AuricExcelLog

....which redirects to (watch the wordwrap):

https://groups.google.com/d/msg/micr...ming/roRHP_loX
xg/31Q6VDwkggAJ

The code there logs all changes made to a given workbook if placed in the
ThisWorkbook object, creating a new hidden log worksheet every time the book
is opened. (Make sure you include the edit I added afterwards or you might
not like the results.)

The code is public domain; feel free to use and/or modify as you see fit.
It's overkill for your needs, but what the hell. Note that the code expects
to be run on a 32-bit version of Office, since that's all I had at the time.

--
Cows would live a lot longer if they weren't made out of steak and leather.