cell fill color update with date difference

[k1dr0ck]

im trying to make a workbook that updates the fill color of each row each day that it is opened based on the difference of the date in column O and the current date
everytime it is opened it goes thru the range checks which row has the red fill color then update the no. of red fill color based on the date difference

1. i cant get the correct no. of red fill color update for the row
2. how can i make it also that it only update the fill color up to column N (if the date difference is greater that it will get past the column N)
3. i manually put the fill color red on rows in column A, what is the best way to trigger automatically putting the current date on column O after putting the red fill color in column A?

i have attached my workbook with the before and after

Please Login or Register  to view this content.

[Artik]

You ran out of resetting the variable i after Next cell.
Corrected code in the attachment.

Please Login or Register  to view this content.

Comments:
- don't keep this type of code in event procedures. Extract them into separate procedures in a standard module.
- if you declare variables on one line, you should specify the type for each of them. Otherwise only the last one has a type defined by you and the rest is Variant.
- if you are deleting lines in a loop, you have to do it in a loop with decreasing increment. Otherwise some lines may be omitted from checking.
- solved the problem of coloring column A when the cell in column O was changed.

Artik

[k1dr0ck]

hi, thanks for the reply and the comments

sorry what I meant was after manually filling red color on column A it would trigger to auto input the current date on column O

- solved the problem of coloring column A when the cell in column O was changed.

i tried the file and it does not add the correct number of red fill color and I think its about the roundup date difference
I found a way to actually get the "day difference" between two dates using DATEDIF as you can see below in the pic on column U but I don't know how to incorporate it into the code
the number in column U is the red fill color that should be added in addition to the initial red fill color

basically using the date difference:
row 2 - should be 5 red fill colored (1+4)
row 3 - correct 10 red fill colored (1+9)
row 4 - should be 2 red fill colored (1+1)
row 5 - correct 6 red fill colored (1+5)

[Artik]

Replace procedure to

Please Login or Register  to view this content.

Artik

[k1dr0ck]

thanks the fill color update now works correctly!

so I tried editing the worksheet change section to insert date on column O after I manually fill colored a row in column A but I can't get it to work

Please Login or Register  to view this content.

tried also this but does not work

Please Login or Register  to view this content.

[Artik]

Coloring cells alone does not trigger sheet events. Ie. I am familiar with two methods to force the event, but I wouldn't like to use them because they are a bit crazy.
Or maybe change the philosophy of operation? Manually insert the date in the O column (using the keyboard shortcut Ctrl +; ) and the triggered event will color the cell in column A. But here comes another question. In the O column, you are satisfied with the date, or must there be a date and time? If the latter, the matter is a bit more complicated because you would have to use the following key sequence: Ctrl +; next Space and then Ctrl + Shift +: .
The Worksheet_Change event will also be fired when you copy a previously prepared cell and paste into the cell in column A.
Yet different. You can color the cell with the button macro. The selected cell is colored and the date is inserted in the O column.

You have a couple of options. Now choose which way you want to go.

Artik

[k1dr0ck]

thanks for the tip use a button and create a separate sub

Please Login or Register  to view this content.