Change cell color every 7 days [Closed]

 vertix -
Is there any way to change a cell's color once a week on a certain day on a regular basis. Example, only on Wednesdays cell color changes to pink automatically. Then reverts back to its original color on every other day untill Wednesday comes around again, and so on. . .

1 reply

Registration date
Sunday June 14, 2009
Last seen
July 30, 2015
try this. I could not test as I have to wait for seven days to completely test. Today 14th October it works.

first type in A1

right click the tab of the sheet and click view code. and in that window copy this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub

If Weekday(Range("A1")) Mod 7 = 4 Then
Range("B1").Interior.ColorIndex = 3
Range("B1").Interior.ColorIndex = xlNone

End If
End Sub

everyday automatically the entry in A1 will change to that day
now when the week day of A1 becomes Wednesday the color in B1 will change to red.

check for a few days and confirm whether it is ok
Thanks dude. You obviously put a lot of effort into this formula, but I was hoping for something simpler which I could insert into certain cells of my choice via 'conditional formatting' of the individual cells, etc. What you gave my up there is way over my head.
> kusart
Hi Kusart,

Use this formula to determine the day (in cell A1 for example):
This formula give you a number: Sunday=1, Monday=2 etc.
Now you can use conditional formating.
Use formula is, and input A1=4 (4 for wednesday) and apply your color.
For your next condition use this formula: A1<>4 and apply your color.

Is this the answer you were looking for?

Best regards,
hi i tried ur formula in vb and its working but how i can rest it untill the next date
how i can reset it untill the next date
thank you trowa for the vb formula ,tell me how i can reset the red color in b1 untill next date

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!