Changes after a specific date.

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
I am trying to have cells display red for information that was typed after a certain date. I know that is easy except that I don't wan't the information that already exhists in the cells to be formatted differently, only changes after the date.

Any suggestions?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Click on Format, Conditional Formatting..., etc. You can now proceed to format the font charateristics, as well as the background color.
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
Not quite

I don't want information that has already been inputted to be formatted, only information that has changed in the same cells.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Obviously, you haven't tried out my suggestion. When you do, you will find out that it will do exactly what you need, assuming you learn how to use Conditional Formatting... It is extremely useful! Please, do give it a try, and let us know if you need further instructions or help.
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148

ADVERTISEMENT

I guess that I'm not very good at explaining myself. I only want the format of the cell to change to red if data is entered after certain date. If there is data in that cell prior to the date then I want it to remain black. I use conditional formatting for a lot of reasons but I am having difficulty making this one work.
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
do you have the date somewhere in the file and are you putting in a time stamp when someone enters something?
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148

ADVERTISEMENT

Yes,
The following macro adds the date and time to Cell P8
Private Sub CB_Keeper_Sign_Click()
If Not IsEmpty([V4]) Then
ActiveSheet.Unprotect
[P8] = Now
[V6] = Application.UserName & " - " & Now
With ActiveSheet
.Shapes("CB_Super_Sign").Locked = True
.Protect
End With
End If
End Sub

Any time after that can be the now() funciton.
I know how to make the cells turn red after that date but am not sure how to keep that data black if it hasn't changed.
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
I figured that one way to do that woulb to copy the cell contentes to another location with that macro and then comparing the data between the current cell and the copied one. now how do I use conditional formatting for more than one condition?
I simplified the formula to make things less confusing:

Private Sub CommandButton1_Click()
[A1] = True
Range("A3").Copy
Range("A10").Select
ActiveSheet.Paste
Range("A3").Select
End Sub

Let's say that the value of A3 is 08:00, how do I use Conditional Formatting to change the color of A3 to red if A1 is True and A3 does not equal A10?
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Better for that code would be

Code:
Private Sub CommandButton1_Click()

Range("A1") = True
Range("A3").Copy Range("A10")

End Sub

Then to conditionally format this it will be formula is:

=And(A1, A3<>A10)

Then format red....

of course this is if I am understanding you correctly. Hope this helps!
 

jyokom

Board Regular
Joined
May 24, 2004
Messages
148
Thanks Brian, I am actually making headway. As with most projects, one solution can lead to another issue. How can I automate the conditional formatting to work for each individual cell in a range? i.e. If I the Sub would be:

Range ("A3:B4").Copy Range ("A10")

End Sub

How can if conditionally format A3:B4 to coincide with A10:B11 without having to format each cell individually?

So far I have:
Sub Conditional()
Range("A3:B4").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(A1,A3<>A10)"
Selection.FormatConditions(1).Font.ColorIndex = 3
End Sub
How do you keep A1 as a constant? I know that you could define a string and assign it the value of A1, but how would you add that in the format?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,477
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top