Greater Than Less Than

  • Thread starter Thread starter @Homeonthecouch
  • Start date Start date
@

@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.
 
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
 
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


JE McGimpsey said:
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 said:
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.
Click to expand...
Click to expand...
 
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>
 
Back
Top