Greater Than Less Than

[@Homeonthecouch]

Hello,
I have a sheet that I am entering data into cells B:B
I have an event macro set up to auto time and date in adjacent cells A:A
I would like C:C to calculate the difference between cell b5-b4, b6-b5,
b7-b6 etc. etc.
=IF(ISBLANK(B3),"",B3-B2)
The results in C:C will be both + and - values.
I want to run a condition that will colour the cells defined by either plus
or minus values with the code below.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b:b")) Is Nothing Then Exit Sub

Select Case Target.Column
Case Is = Me.Range("b1").Column
With Target
If .Row = 1 Then
'do nothing
Else
If IsEmpty(.Cells) Then
'do nothing
Else
'don't fire again!
Application.EnableEvents = False
With .Offset(0, -1)
.NumberFormat = "dddd - dd mmmm yyyy - hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End If
End With
End Select

End Sub



Any help is appreicated again as always.

 

[JE McGimpsey]

Not sure why you need to apply formatting using the macro, when you
could use either conditional formatting or custom formats (e.g.,:

[Blue]0;0;[Red]0;@

which would show + in blue, - in Red)

I'd rearrange your macro a bit:

Private Sub Worksheet_Change(ByVal Target As Range)
Const csFORMAT As String = "dddd - dd mmmm yyyy - hh:mm:ss"
With Target
If .Count > 1 Then Exit Sub
If .Column = 2 Then
If .Row > 1 Then
If Not IsEmpty(.Value) Then
With .Offset(0, -1)
On Error Resume Next
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
On Error GoTo 0
.NumberFormat = csFORMAT
End With
End If
End If
End If
End With
End Sub

 

[@Homeonthecouch]

Well as I am totally illiterate with VBA I am using the methods suggested to
me.
I really haven't had a chance to try and understand the coding.
I wanted the cell to colour rather than just changing the text.
I have tried using the conditional formatting using
Cell Value Is greater than 0
and
Cell Value Is less than 0

But as the cell has the formula =IF(ISBLANK(B3),"",B3-B2) which is making
all the cells blue ?!?!?!
That was why I wanted to use the event macro. Also if I wanted to add more
conditions later I would be able to.

Andrew

Not sure why you need to apply formatting using the macro, when you
could use either conditional formatting or custom formats (e.g.,:

[Blue]0;0;[Red]0;@

which would show + in blue, - in Red)

I'd rearrange your macro a bit:

Private Sub Worksheet_Change(ByVal Target As Range)
Const csFORMAT As String = "dddd - dd mmmm yyyy - hh:mm:ss"
With Target
If .Count > 1 Then Exit Sub
If .Column = 2 Then
If .Row > 1 Then
If Not IsEmpty(.Value) Then
With .Offset(0, -1)
On Error Resume Next
Application.EnableEvents = False
.Value = Now
Application.EnableEvents = True
On Error GoTo 0
.NumberFormat = csFORMAT
End With
End If
End If
End If
End With
End Sub

Hello,
I have a sheet that I am entering data into cells B:B
I have an event macro set up to auto time and date in adjacent cells A:A
I would like C:C to calculate the difference between cell b5-b4, b6-b5,
b7-b6 etc. etc.
=IF(ISBLANK(B3),"",B3-B2)
The results in C:C will be both + and - values.
I want to run a condition that will colour the cells defined by either
plus
or minus values with the code below.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("b:b")) Is Nothing Then Exit Sub

Select Case Target.Column
Case Is = Me.Range("b1").Column
With Target
If .Row = 1 Then
'do nothing
Else
If IsEmpty(.Cells) Then
'do nothing
Else
'don't fire again!
Application.EnableEvents = False
With .Offset(0, -1)
.NumberFormat = "dddd - dd mmmm yyyy -
hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End If
End With
End Select

End Sub



Any help is appreicated again as always.

 

[JE McGimpsey]

Using Conditional formatting:

Choose Format/Conditional Formatting... and change the dropdowns and
inputbox to read:

CF1: Formula is =AND(B3<0,B3<>"")
Format1: <patterns>/<color if less than>

CF2: Formula is =AND(B3>0,B3<>"")
Format2 <patterns>/<color if greater than>