Excel VB Script 1

[cooldisk2005]

All,
I am using excel to do a simple If/Then statement, but I don't know what the correct syntax is. I am used to Access, so I need help from someone out there.

I think I go to the VB Editor to do this, but this is what I want:

I have a cell with this formula: =SUM(D3 + 90). This formula is in E3. This is a date in E3 that goes up 90 days from the date that is inputted in D3.

I am duplicating that formula to about 10 cells below, but D4, D5, D6, etc. right now don't have data in them, but it is returning a value of 3/30/1900 in the cells that have the formula.

I don't want anything to be in those cells if there is no data in D4, D5, D6, etc.

Can someone tell me how to accomplish this?

Any help will definitely be appreciated.

Thanks,

 

[arthurbr]

I suppose your formula is =d3+90 ( not sum..)
In E4 ,try =if(d4="";"";d4+90) then pull down

Cheers

 

[cooldisk2005]

It says that the formula contains an error. This is what I put in:

=if(D4="";"";E5+90)

It has a an error.

 

[byundt]

In some countries, the semicolon is used as a separator character in formulas. In other countries, such as United States, the comma is used for the same purpose. Because people from all over the world post in this forum, there is frequently a problem with formulas not working because the wrong separator character was used.

In the US, if you want to return either a blank or D3+90, then you would put this formula in cell E3:

=IF(D3="","",D3+90)

This formula may be copied down to subsequent rows. To copy a formula down:
1) Select the cell containing the formula
2) Point the cursor to the little square at lower right of the selection marquee
3) Hold the left mouse button down, then drag the little square to the last cell you want to receive the formula. Release the mouse button when the cursor is over that cell.

Brad

 

[cooldisk2005]

That was perfect!!!

I have one more thing I want to accomplish and hopefully you can help me with this one as well:

If the date in the E column has passed without a date in the F column, then I want the E column cell background to change to red and the text font color to change to white.

If I input a date in the F column cell then I want the E column background to change back to white and the text font color to change to black.

Can something like this be done?

 

[byundt]

You will find Conditional Formatting very useful for changing the color of the background or font. To create Conditional Formatting, select all the cells in column E that will receive the formatting, then open the Format...Conditional Formatting menu item.
Select "Formula Is" in the left field, then enter the following formula:

=AND(E2<>"",F2="",TODAY()>E2)

Note: change the reference to cell E2 to the first cell in your selected range.

Click the Format button, then the Patterns tab. Choose your Red highlight color, then click on the Font tab and choose your white font color. Then click OK twice.

You probably don't need to add a Conditional Format for your second set of conditions because it sounds like you really want the normal formatting to occur.

If the results don't appear quite right, check the Conditional Formatting formula to make sure that Excel didn't add extra quotes or absolute references in its zeal to be helpful. Correct any such errors, then click OK again.

Brad

 

[cooldisk2005]

I tried what you suggested, but it is not working. I have placed the file on this site:

http://www.jerpat.org/AffiliateFile.xls

If you can, will you please take a look at it and let me know what is going on. I have data in F2, but the colors in E2 didn't go back to the default colors.

 

[byundt]

You need to set the regular formatting in column E to no highlight and black font.

If you do this, then Conditional Formatting will apply when the three conditions are satisfied (column E date is less than today, columns E and F are not blank). Otherwise, the regular formatting applies.

 

[cooldisk2005]

byundt,
You are GOOD!!!

That all worked perfectly!!!

Be looking for a star my friend!!!

Thanks so much!!!