VBA to change background color of cell. color based on day of the month

[Jay Merritt]

I have current code to change the background of the cell I updated "Today" to the color of the day. Today is the 15th of the month, so todays color is 15. What I am looking for is the "15" to change based on the day, so tomorrow the code would change to 16 without me going into the code daily and updating.

Private Sub WorkSheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 15


'use 1 - black with codes 2,4,6,8,15,19-20,24,27
Target.Font.ColorIndex = 1


'use 2 - white with codes 1,3,5,7,9-12,13-14,17-18,21,25-26,29-31
'Target.Font.ColorIndex = 2
End Sub

 

[Rick Rothstein]

'use 1 - black with codes 2,4,6,8,15,19-20,24,27
Target.Font.ColorIndex = 1

'use 2 - white with codes 1,3,5,7,9-12,13-14,17-18,21,25-26,29-31
'Target.Font.ColorIndex = 2

Did you really mean 9-12,13-14 for color 2? If so, why didn't you list it as 9-14?

What color should codes 16, 22, 23 and 28 get?

 

[My Aswer Is This]

It would seem to me this is what your wanting.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/15/2019  5:33:15 PM  EST
Target.Interior.ColorIndex = Day(Date)
End Sub

 

[Rick Rothstein]

It would seem to me this is what your wanting.

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  'Modified  1/15/2019  5:33:15 PM  EST
  Target.Interior.ColorIndex = Day(Date)
[B][COLOR="#FF0000"]  Target.Font.Color = -vbWhite * (77 * (Target.Interior.Color Mod &H100) + _
                   151 * ((Target.Interior.Color \ &H100) Mod &H100) + 28 * _
                   ((Target.Interior.Color \ &H10000) Mod &H100) < 32640)[/COLOR][/B]
[B][COLOR="#0000FF"]  Target.Font.Bold = (Target.Font.Color = vbWhite)[/COLOR][/B]
End Sub[/td]
[/tr]
[/table]

If you include the code I added in red to My Aswer Is This' event procedure above, the font will automatically be black for light colored fills and white for dark color fills. I would also suggest including the code I show in blue above as that will automatically make the white font bold so it will be more easily readable.

 

[My Aswer Is This]

I missed font color in previous post.

I would try this:

You may need to modify code it appears to me you missed a few dates.

You should see the ideal.

It's best to let you modify any needed changes it will help you learn Vba.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/15/2019  7:41:05 PM  EST
Target.Interior.ColorIndex = Day(Date)
Select Case Target.Interior.ColorIndex
    Case 2, 4, 6, 8, 15, 19, 20, 24, 27
        Target.Font.Color = vbBlack
    Case 1, 3, 5, 7, 9, 10, 11, 12, 13, 14, 17, 18, 21, 22, 23, 25, 26, 29, 30, 31
        Target.Font.Color = vbWhite

End Select
End Sub