Excel VBA to compare 2 dates, evaluate cell contents, and enter value

[Doug Mutzig]

Good afternoon all,

I have a workbook that has several tabs. Each tab has 1 table of information, and each tab is for use by an individual unit. The table has several columns and rows of information including one column that contains dates (DEROS column), there are also columns that contain a date in the header (in text format since headers are formatted that way in tables). What I would like to happen is for each cell in a date headed column a check is done that looks to see if the cell is blank and if blank to compare the row DEROS date to the column header date and if the DEROS date is greater than the Header date enter a 1. If the cell already contains something (could be 1, 0, .5, 1*, etc.) do not overwrite.

I have a formula that I can use in the cells

=IF(DATE(YEAR($B3),MONTH($B3)<wbr>,1)>VALUE(H$2),1,"")

, however, end users will be entering numbers manually which would overwrite the formula.

I would like to use a button to manually run the script and be able to run it on any of the tabs (i.e. not set to a specific table). I believe since there is only 1 table per tab I could just have the script look at the active sheet, find the table, and run.

To make it more complected the DEROS column can have a variable amount of rows and there can also be a variable amount of date columns. I think I could get the script to go to the DEROS column and then go from the bottom up to get the last entry however I am not sure how. For the variable date columns I am at a loss.

I have added a table that hopefully shows what I am talking about.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DEROS[/TD]
[TD]Name[/TD]
[TD]Skill[/TD]
[TD]Aug-2019[/TD]
[TD]Sep-2019[/TD]
[TD]Oct-2019[/TD]
[TD]Jan-2020[/TD]
[TD]Feb-2020[/TD]
[/TR]
[TR]
[TD]9/15/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/5/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/20/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/6/2020[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD].5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you all for your help on this!
Doug