VBA or Conditional Formatting

[Vxuser]

Hello to everyone,

I was wondering if there is any VBA code or Conditional Formatting for changing the Fill Collor of a cell after I wrote in it. The trick is that I want this to work with a timer. What I mean is that at midnight the cell to become filled with red color and after I wrote something the cell to become white. Next day at midnight again, cell red and after I write something to become white. I need something like this because every day I have to make a report and in order for me not to forget any cell that I have to write it needs to be red and after I updated the cell to become white.

Thank you.

 

[My Aswer Is This]

Using Vba this could be done.
But instead of Midnight how about when you enter the value Red into Range("A1")
After that the script starts running

Using Midnight would mean Excel must be running at exactly Midnight to make the change.

And tell me what range of cells are we talking about.
Do you mean any cell on the Sheet name "Master" or a Range of cells on the sheet named Master.

For example the Range named ColorMe

So if in Range("A1") you enter Red all cells in the Range named ColorMe would turn Red. And then after all cells in the range when changed would turn White.
If you enter any other value in Range("A1") all cells in the Range ColorMe are set back to white
Would this work.

If so try this script:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/20/2019  9:26:27 PM  EDT
If Target.Cells.CountLarge > 1 Then Exit Sub
If Target.Address = Range("A1").Address Then
If Target.Value = "Red" Then
    Range("ColorMe").Interior.Color = vbRed
    Target.Interior.Color = vbRed
Else
    Range("ColorMe").Interior.Color = xlNone
    Target.Interior.Color = xlNone
End If
End If
If Not Intersect(Target, Range("ColorMe")) Is Nothing Then
    Target.Interior.Color = xlNone
End If
End Sub

 

[Vxuser]

Thank you for the interest of helping me.

I have uploaded my file so to have a better understa ding of what I really want to make.
So at the moment if you look in NOON page you will see some cells with red filling color. I have done this with Condition Formatting. But when I fill the cells with new data nothing withh change unless I click the button Date AT Noon. Once I click this one all cells will become white.

So what I want is a VBA code that every day at midnight (when a new day starts) the cells that are in my excell form to become red but when I tyle something to become white. And next day at midnight to reset again to red and again when I will type something in them to become white

https://gofile.io/?c=Dt60Br

Password for unlocking the sheet is: NoPassword
Password for unlocking the VBA is: No Password

Thank you once again!

 

[My Aswer Is This]

I never open files posted to this forum.

I gave you a suggested way. But I guess that did not work for you.
Having the script always work at exactly midnight is not something I know how to do.

Did you even try my solution. And like I asked before I would need to know the exact range we are dealing with.

 

[Vxuser]

Sorry for my late reply.

I did try your script but it is giving error when i input something in the range A1.
My actual range of cells that i need is: C5:C9 and C14:C17 and D14:D16 and E9,E11, E17. All of these cells I need to change daily so all of them need to change the color daily.g

Thank you!

 

[My Aswer Is This]

You need to select those ranges and then name them "ColorMe"

And then enter "Red" into A1 at beginning of each Day like I mentioned in my first post.

Did you follow my instructions in my first post.

I cannot have the script constantly check to see what time it is 24 Hours a day to see when it is exactly midnight.

Is it impossible for you to enter Red into Range("A1") at beginning of each day.

I could have the script enter Red into "A1" when workbook is opened would that work.

 

[Vxuser]

I have waited a day in order to test the script but nothing happened. I guess is not working or I am doing something wrong.
I have put Red in A1 and waited 1 day not nothing happened enev after i defined the range of cells.

Thank you!

 

[My Aswer Is This]

After putting Red in Range("A1") the Range named MyColor turns Red. You must enter some values in the range.
When you enter a value in the range the cells Interior color turns white

You must enter the values manually.
Are you saying you entered Red into Range("A1") and the range did not turn Red?