Freezing(?) cells

M

Mark Long

Hello,
I need to set up my worksheet so that if H20<4000 H24 shows "A Charge"
but does not change back once H20 >4000 again, so that when I produce a
printout I'll know if there has been a charge incurred at some point. I have
it set up to work to the point where H24 shows "A Charge", but as soon as I
get back above 4k, it goes back to "No Charge". How can I freeze (right
term?) H24 so that it will show up correctly on a printout should the
conditions have arisen?
 
J

jeff

Hi,

I think you can only do this with VBA (trying with
a formula gets you a circular reference).

right click on the tab name, select 'view code'
paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H24").Value <> "A Charge" Then If Range
("H20").Value < 4000 Then Range("H24") = "A Charge"
End Sub

(lines 2 & 3 should be on one line) YOu can modify/
expand the cell references to include other rows/columns.

jeff
 
M

Mark Long

jeff said:
Hi,

I think you can only do this with VBA (trying with
a formula gets you a circular reference).

right click on the tab name, select 'view code'
paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H24").Value <> "A Charge" Then If Range
("H20").Value < 4000 Then Range("H24") = "A Charge"
End Sub

(lines 2 & 3 should be on one line) YOu can modify/
expand the cell references to include other rows/columns.

jeff
Click to expand...

Hello Jeff,
Well waddya know that works a treat. Much obliged, Sir! I knew it was
probably going to need vba, but wouldn't know where to start with it, so
thanks.
Mark
 
Top