If statement

[jobo]

I have produced an excel doc which I am having probs with.
I have entered the formula =now() into cell G1.
I have entered a date later than now into cell G2.
I have entered the formula =IF(G1>G2,0,1) in cell G3.

With this configuration if G2 is later than now then G3=1 and if G2 is earlier G3=0.
I am now trying, without success, to make all cells in the doc = 0 if G3=0 pos using a conditioning formula.

Can Anyone help

[shg]

Please edit the title of your post to provide a meaningful description of your problem.

[Paul]

I am now trying, without success, to make all cells in the doc = 0 if G3=0 pos using a conditioning formula.

What do you mean 'make all cells in the doc = 0'? All cells as in A1:IV65536? A specific range of cells? A row?

Please provide some more detail.

Thanks.

[jobo]

Thanks for your quick response.

At present I have cells A4:Y270 which have various entries some of which are text and some numbers. I want all of these cells to have a value of zero if todays date is later that a predetermined date. At present the formula in cell G1 is =now() and cell G2 is the predetermined ie 11/06/2009. If I enter the formula =If(G1>G2,0,1) in cell G3 it will give a value of one in cell G3 at present and a value of zero when NOW is later than 11 june 2009. After 11 June 2009 I would like cells A4:Y270 to equal G3 ie zero.
Perhaps I have gone in the wrong direction to achieve the end result and you can think of another way of making the cells equal zero after 11 June 2009.

Thanks Again and I hope this make my problem clearer

Jobo

[Paul]

You can do what you're asking by using a macro. Formulas won't work, since when you replace A4:Y270 with 0's the formulas would disappear.

The following code checks, upon opening the workbook, to see if Sheet1 cell G1 is greater than Sheet1 cell G2. If not, nothing happens; if so, A4:Y270 are all changed to 0's.

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        If .Range("G1").Value > .Range("G2").Value Then
            .Range("A4:Y270").Value = 0
        End If
    End With
End Sub

[jobo]

I have tried the above on my PC but can’t get it to work probable because I have done it wrong.
When I try it on my work PC it tells me I am not authorised to use macro and I am unable to change this.
As the excel I am trying to make is for work are there any other suggestions.

[Paul]

Unfortunately if you can't run macros, and you can't change the security settings to allow you to run macros (Tools -> Macro -> Security), your only option to quickly change A4:Y270 to 0's is this:

Upon opening your file, look to see if G1 > G2. If so, then press F5, and in the reference box type A4:Y270 and click OK. Type the number 0, and press CTRL+ENTER. That will put a 0 in every cell selected.