Put NA on week dates not in range

AaliyahN82

New Member
Joined
Apr 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
So I am working on a project that is connected to an Access database. On button click it will retrieve everything that's in my Access table to my Excel file then it will create a table which shows which weeks each person has data in the database. If they have data for that week (week beginning is always a Monday), it will show 1 and if there's none, it will show 0. Then all 1s will be converted to Y and all 0s will be converted to N.

However, I needed to add two columns on my data sheet (which contains a list of names of the people I am checking the above against) saying Week Start Date and Week End Date. The Week Start Date is the week date (week beginning Monday) when the person initially sent data, and the Week End Date is the week date (week beginning Monday) the person is set to send data.

I need to revamp the below code to also check the two columns. Like the Y/N will only start on the date indicated in the Week Start Date column and will end on the date indicated in the Week End Date column. The rest of the dates will show as NA. However if the two columns are empty then it will do Y/N as usual.

here's my code:

VBA Code:
For Each cell In rng
    If cell.Value = "0" Then
        'write to adjacent cell
       cell.Value = "N"
        cell.Interior.Color = 13551615
       Else
        cell.Value = "Y"
        cell.Interior.Color = 13434828
    End If
Next

I also posted here: Put NA on week dates not in range
and in vbaexpress however the site is currently down and could not put a link to the thread that's been unanswered for maybe 4-5 days already.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,122,977
Messages
5,599,151
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top