VBA or Conditional Formatting
Results 1 to 8 of 8

Thread: VBA or Conditional Formatting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA or Conditional Formatting

    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.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA or Conditional Formatting

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Conditional Formatting

    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!

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA or Conditional Formatting

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Conditional Formatting

    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 by Vxuser; Jul 22nd, 2019 at 04:39 PM.

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA or Conditional Formatting

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA or Conditional Formatting

    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!

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA or Conditional Formatting

    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?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •