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

Jay Merritt

New Member
Joined
Jan 15, 2019
Messages
1
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
'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

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
It would seem to me this is what your wanting.
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
It would seem to me this is what your wanting.
Code:
[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.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
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.

Code:
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
 

Forum statistics

Threads
1,081,624
Messages
5,360,093
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top