Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-24-2018, 09:03 AM
vvenka100 vvenka100 is offline Place on value in a cell from that time, after 4 hours colour of the cell should change Windows 10 Place on value in a cell from that time, after 4 hours colour of the cell should change Office 2016
Novice
Place on value in a cell from that time, after 4 hours colour of the cell should change
 
Join Date: Apr 2018
Posts: 1
vvenka100 is on a distinguished road
Default Place on value in a cell from that time, after 4 hours colour of the cell should change

The moment i place the value in the cell that cell colour to change to green for example.
then, after exactly 4 hours that cell colour should change to RED . i tried with multiple options but no luck, could anyone please help me to achieve this .
Reply With Quote
  #2  
Old 04-26-2018, 01:13 PM
p45cal's Avatar
p45cal p45cal is online now Place on value in a cell from that time, after 4 hours colour of the cell should change Windows 10 Place on value in a cell from that time, after 4 hours colour of the cell should change Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

You can, sort of.
In the attached, there's a range of cells (B2:E13) with a border around that will, more or less, do as you ask.
If you enter or change a value in any of the cells in that range, it will turn green, then conditional formatting is added to turn it red later (10 seconds), BUT it won't change unless:
  • another cell is changed anywhere on the sheet
  • the sheet is recalculated
  • the selection on the sheet changes
  • the sheet is activated (as in when you've been looking at another tab)
As it stands, any previous conditional formatting will be removed

If you delete the contents of a cell it will have its conditional formatting removed and its colour returned to no fill.

The code in the sheet's code module is:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Intersect(Range("B2:E13"), Target)
If Not myRng Is Nothing Then
  myThen = CDbl(Now() + TimeValue("00:00:10"))
  For Each cll In myRng.Cells
    If Len(cll.Value) > 0 Then
      cll.Interior.Color = 11854022
      With cll.FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=NOW()>" & myThen
        .Item(1).Interior.Color = 255
      End With
    Else
      cll.FormatConditions.Delete
      cll.Interior.ColorIndex = xlNone
    End If
  Next cll
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub
To change the delay to 4 hours, change the:
TimeValue("00:00:10")
to:
TimeValue("04:00:00")
Attached Files
File Type: xlsm msOfficeForums38878.xlsm (18.2 KB, 7 views)

Last edited by p45cal; 04-27-2018 at 02:29 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Place on value in a cell from that time, after 4 hours colour of the cell should change How to change the colour of a cell Goozzie Excel 3 03-17-2018 07:11 AM
Change Text Colour in Cell Based on Text in Same Cell PMC11 Word VBA 1 11-14-2017 09:15 PM
Place on value in a cell from that time, after 4 hours colour of the cell should change Clear all cell colors within a range starting at cell A8 and change row of active cell to yellow FUGMAN Excel Programming 7 02-05-2017 08:37 AM
VBA to immediately change the colour of a cell depending on the code placed in anothe Phil Payne Excel Programming 2 07-27-2013 11:04 PM
CHange colour of footer if a cell changes to red OTPM Excel 0 05-26-2011 07:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft