Macro to Clear Cells based on condition

[CherylHolubar]

I need a macro that auto runs when the spread sheet is opened with the following formula as a condition: =IF(LEFT($B$2,5)>=LEFT(A35,5) if this is true, then I want it to clear the selected cells, if false then "". I am a novice using macros and all I have written is simple macros that format and populate cells based on line counts. I have never used a formula in a macro. The formula above is based on dates.

 

[sijpie]

To run an auto macro, you need to enter the macro in the Workbook object

Open the macro editor. On the left is a list of open workbooks, with your workbook amongst them. DoubleClick on the ThisWorkbook of the workbook where you want to add the auto macro.

The editor will show a blank screen. Just above the cursor you see a dropdown box with '(General)'. Click on it and select 'Workbook'.
A new sub will be written called Workbook_Open()

This will run when the workbook opens.

So now you can do your stuff in here. You want to compare two values, but you need to specify which sheet these values are in, in case someone closed the workbook on a different sheet. Let's assume the data you have is on Sheet1.
Then you say you want to 'Clear the selected cells'. The workbook is opening, so how can you know what the selected cells are? I am assuming you want to clear the cells mentioned in your comparison:

Private Sub Workbook_Open()
    
    If Left(Worksheets("Sheet1").Range("B2"), 5) >= Left(Worksheets("Sheet1").Range("A35"), 5) Then
        Worksheets("Sheet1").Range("B2") = ""
        Worksheets("Sheet1").Range("A35") = ""
    End If
End Sub

You mention the 'formula is based on dates'. Do these two cells contain dates (as in real Excel dates, not text strings)? then you can just compare the values:

Private Sub Workbook_Open()
    
    If Worksheets("Sheet1").Range("B2") >= Worksheets("Sheet1").Range("A35") Then
        Worksheets("Sheet1").Range("B2") = ""
        Worksheets("Sheet1").Range("A35") = ""
    End If
End Sub

 

[CherylHolubar]

Thanks for your quick reply. I'm going to try this when I get back to the office tomorrow.

I'll let you know if I'm able to do this. In the formula I had, instead of using a wildcard in the year of the date, I'm having it compare the left 5 characters. I believe I got this from this website. Thanks again.