VBA or Conditional Formatting

Vxuser

New Member
Joined
Jun 29, 2019
Messages
9
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

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

New Member
Joined
Jun 29, 2019
Messages
9
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

New Member
Joined
Jun 29, 2019
Messages
9
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!
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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

New Member
Joined
Jun 29, 2019
Messages
9
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,660
Messages
5,488,159
Members
407,628
Latest member
Faceless Judge

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top