change cell color every 7 daysClosed

Question

Hello,
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. . .

venkat1926 · Answer

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 
=today()

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
Else
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

Change cell color every 7 days

1 response

Subscribe To Our Newsletter!