Change Background cell color depending on date and last value of c

[Guest]

()My knowledge is in Access, but I have been asked to assit in making an
Excel spreadsheet cell do the following:

On Change of the cell value, if the previous value of the cell was null or
blank - change the background color to:
If now() less (a date in the current column row 7) <= 30 yellow
if now() less (a date in the current column row 7) >= 30 Green
else :if it had a value leave the color as is

I have now spent a considerable time trying to accomplish this and am no
further forward. Can anyone help?

 

[Jim Cone]

Right-click the sheet tab and select View code.
Paste in the following code.
You will need to alter the code to reflect the correct cell
and to enter the code to handle the dates/colors.
See the lines marked with <<<
What is provided is "Event" code that automatically executes
when a change is made to any cell.
'----

Private varOldValue As Variant 'place this line at top of module.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitProcess
If Target.Address = Me.Range("B5").Address Then '<<<<
Application.EnableEvents = False
If Len(varOldValue) = 0 Then
'change some colors <<<<
End If
varOldValue = Target.Value
Application.EnableEvents = True
End If
Exit Sub

ExitProcess:
Application.EnableEvents = True
End Sub
----
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"theBruceGuy" <[email protected]>
wrote in message
()My knowledge is in Access, but I have been asked to assit in making an
Excel spreadsheet cell do the following:

On Change of the cell value, if the previous value of the cell was null or
blank - change the background color to:
If now() less (a date in the current column row 7) <= 30 yellow
if now() less (a date in the current column row 7) >= 30 Green
else :if it had a value leave the color as is

I have now spent a considerable time trying to accomplish this and am no
further forward. Can anyone help?

 

[Guest]

Thanks Jim for the response, however my Excel coding ability is worse than I
thought

I have entered the code supplied and tested it to the best of my ability.

I need to check that the Target.address is within the range CM8U607. I
have tried various versions of the line "If Target.Address =
Me.Range("B5").Address Then " but always skip to "End if" after it.

How do I write this line.

 

[Jim Cone]

The problem has changed from what I understood.
Dealing with a single cell location is different than dealing with a range of cells.
To answer your specific question...

"If Not Application.Intersect(Target(1, 1), Me.Range("CM8U607")) Is Nothing Then"

Intersect returns a range object, if it is nothing then there is no overlap.

It appears you will have to somehow retain the value of each cell in the range
and then compare the changed cell (Target) new value to its old value.
Maybe with Array contained in a Variant.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"theBruceGuy" <[email protected]>
wrote in message
Thanks Jim for the response, however my Excel coding ability is worse than I
thought

I have entered the code supplied and tested it to the best of my ability.
I need to check that the Target.address is within the range CM8U607. I
have tried various versions of the line "If Target.Address =
Me.Range("B5").Address Then " but always skip to "End if" after it.

How do I write this line.

 

[Guest]

Thanks Jim,

I'm certainly learning!!!!!

Your code was more professional than my poor attempt of "If Not
(Target.Address < Me.Range("CM8").Address) Or Not (Target.Address >
Me.Range("CM8").Address) Then " though mine also appeared to work.

My challenge now is, as you said, getting the old value. In Access I can
refer to the previous value of a field right up to the point of the field
afterupdate event, and roll back to it if it doesn't match the criteria in my
code. Is there no similar method in Excel?

I only need to know what the value of the target or active cell was on
selection, then if I change that value, change the cell background depending
on criteria I set.

Many thanks

Graham

 

[Guest]

Wow, I think I have cracked it.............

I added the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varOldValue = Target.Value
End Sub

Now it all works great.

Thanks for all the assitance Jim.
PS I liked your web site. Your programs are really good.

Graham

 

[Jim Cone]

Graham,

Thanks for the kind words about my website.

I believe your code is going to need another tweak or two.
(note the difference in the use of the range address: CM8 vs. CM1)...
'---
Private varOldValue As Variant


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitProcess
Dim lngRow As Long
Dim lngCol As Long
If Not Application.Intersect(Target(1, 1), Me.Range("CM8U607")) Is Nothing Then
Application.EnableEvents = False
lngRow = Target(1, 1).Row
lngCol = Target(1, 1).Column - Me.Columns("CL").Column
If Not IsEmpty(varOldValue) Then
If Len(varOldValue(lngRow, lngCol)) = 0 Then
'change some colors <<<<
End If
End If
varOldValue = Me.Range("CM1U607").Value
Application.EnableEvents = True
End If
Exit Sub

ExitProcess:
Beep
Application.EnableEvents = True
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"theBruceGuy"
<[email protected]>
wrote in message
Wow, I think I have cracked it.............

I added the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varOldValue = Target.Value
End Sub

Now it all works great.

Thanks for all the assitance Jim.
PS I liked your web site. Your programs are really good.

Graham