Need macro to calculate number of days elapsed between data in two columns

Hi all,

I am creating a macro to work with an excel file that will be downloaded monthly from an online database.

This portion of the macro will deal with two columns (which are already created in the Excelsheet) described below:

a) Headed "todaydate"; always in column x; range: x2:x300; will be populated with today's date (mm/dd/yyyy)

b) Headed "createdate"; column location varies; range: _2:_300; will be populated with the data's create date (mm/dd/yyyy)

I need the macro to calculate the number of days elapsed between createdate and todaydate within each row and highlight the entire row when the number of elapsed days is greater than 60.

Though I understand the basics behind finding a column based on the header value, subtracting cell values from each other, and highlighting rows, I am having difficulty putting all these concepts together for the macro. I have searched this forum and Google more generally for a solution to the problem to no avail.

Thanks so much for the help.

Hi

This is wrong.

All you need is a conditional format.

Hi,

I am leaving my department in a few months and someone else will need to download and analyze the data. Though I am not highly skilled in Excel, the others in my department are basically computer illiterate. I am already making a macro for other parts of the analysis, and want to make the process as easy and sustainable as possible for after I leave. Thus, I would prefer to incorporate this aspect of the processing into my macro rather than teach the others in my department how to use conditional formatting and run a macro simultaneously.

Thanks

All I did here was to select all the cells

Select the home tab and then conditional format

Insert new rule

Format determined by formula

Then I entered this formula:-

=$B1-$A1 >= 60

Then I selected my format to FILL with Red.

Thanks so much for the help but I need a macro. See above.

Now If you want to do that using a macro

Try this code:-

Sub Macro5()
With Cells
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=$b1-$A1>=60"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        StopIfTrue = False
    End With
End With
End Sub

As I mentioned above, the column "createdate" will not always start in the same location (e.g. B1), but rather will change depending on how the user downloads the data from the online database. Dealing with this issue is what I am most confused about. The column "todaydate", however, will always start in X1. Your code assumes the columns exist in B and A.

Does anyone know how to address this issue? Thanks