VBA?

  • Thread starter Thread starter PeCoNe
  • Start date Start date
P

PeCoNe

In start a new Excel sheet with Excel 2010.
I need to check cell B2 on open if it contains the date of today.
If not I need to place today's date in B2 and clear the cells
D3,F3,H3,I3 and D4:I4.
During the day cell F4 receives a new value and the cell F3 the time.
I need to keep the lowest value in D4 and the time in D3 and
I need to keep the highest value in H4 and the time in H3

I think that is possible with VBA.
The problem is: I don't know anything of VBA.

So please help.
 
Hi,
You will need to have one macro to run when the workbook opens and another to run when the specific cell changes.
To get you started, read: http://www.ozgrid.com/VBA/run-macros-change.htm
to get the "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
and it is analogous to get the workbook open event "Private Sub Workbook_Open()"
For your workbook open event, you would need code as such:
if Range("B2").value <> Date Then
Range("B2").value = Date
Range("D3").ClearContents
Range("F3").ClearContents
Range("H3:I3").ClearContents
Range("D4:I4").ClearContents
End if

For your update event, use the first two line from the linked example from OZGRID (replacing A1 with F4);
Then use code similar to this.

if Range("F4").value < Range("D4").value Then
Range("D4").value = Range("F4").value
Range("D3").value = Range("F3").value
End if
if Range("F4").value > Range("H4").value Then
Range("H4").value = Range("F4").value
Range("H3").value = Range("F3").value
End if

Hope this helps. Sites like OZgrid and http://www.mrexcel.com/forum/excel-questions/ can help build your knowledge of VBA.
Cheers,
Rob.
 
Back
Top