Excel VBA not recognizing value of cell

[williamu]

Vba does recognize when the cell value changes in B7 to R7 because I pull cell value from another worksheet. it only works if I change the cell directly. any suggestions.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rng As Range
Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
If Not Intersect(Target, rng) Is Nothing Then
For Each c In rng
If c < 0 Then
c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 3
ElseIf c > 0 Then
c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 4
Else
If Not c.Borders.linestyle = xlNone Then
c.Offset(-2).Resize(4).Borders.linestyle = xlNone
End If
End If
Next
End If
End Sub[/FONT]

 

[Jeffrey Mahoney]

Worksheet_Change only gets triggered when a user changes a cell, not when the calculated value of a cell changes. Worksheet_Calculate() gets triggered every time the sheet is recalculated. You would have to track the value of the cell to see if it changed from the last time. You could store the value of the target cell and check against that.

 

[williamu]

Can't get to work, getting error

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("B7, D7, F7, H7, J7, L7, N7, P7, R7").Value <> OldVal Then
OldVal = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7").Value
If Target.Cells.Count > 1 Then Exit Sub
Dim rng As Range
Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
If Not Intersect(Target, rng) Is Nothing Then
For Each c In rng
If c < 0 Then
c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 3
ElseIf c > 0 Then
c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 4
Else
If Not c.Borders.linestyle = xlNone Then
c.Offset(-2).Resize(4).Borders.linestyle = xlNone
End If
End If
Next
End If
End If
End Sub
[/FONT]

 

[Jeffrey Mahoney]

Getting the value of multiple cells is not a valid way.

If Range("B7, D7, F7, H7, J7, L7, N7, P7, R7").Value <> OldVal Then

You would have to each individual value to test

Same for this:

OldVal = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7").Value

What is the reason for loading OldVal if you're not going to test it against something?

This is the way I see this routine working. It still doesn't use OldVal in any way. I was expecting that you would save the values for each individual cell and test if the values have changed since the last recalc

Private Sub Worksheet_Calculate()
  Static OldVal As Variant
  Dim rng As Range
  Dim c As Range
  
  Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
  If Intersect(Target, rng) Is Nothing Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  
  For Each c In rng
    If c < 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 3
    ElseIf c > 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 4
    Else
      If Not c.Borders.LineStyle = xlNone Then
      c.Offset(-2).Resize(4).Borders.LineStyle = xlNone
    End If
  Next c


End Sub

Did you know that you could use Conditional Formatting to do what you are trying to do with a macro?

Jeff

 

[williamu]

Jeff,

getting compile error "next without for"

 

[Jeffrey Mahoney]

I couldn't test it, but I should have seen it.

Private Sub Worksheet_Calculate()
  Static OldVal As Variant
  Dim rng As Range
  Dim c As Range
  
  Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
  If Intersect(Target, rng) Is Nothing Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  
  For Each c In rng
    If c < 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 3
    ElseIf c > 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 4
    Else
[COLOR=#ff0000]      If Not c.Borders.LineStyle = xlNone Then[/COLOR]
[COLOR=#ff0000]        c.Offset(-2).Resize(4).Borders.LineStyle = xlNone
      end if[/COLOR]
    End If
  Next c




End Sub

 

[williamu]

I couldn't test it, but I should have seen it.

Private Sub Worksheet_Calculate()
  Static OldVal As Variant
  Dim rng As Range
  Dim c As Range
  
  Set rng = Range("B7, D7, F7, H7, J7, L7, N7, P7, R7")
  If Intersect(Target, rng) Is Nothing Then Exit Sub
  If Target.Cells.Count > 1 Then Exit Sub
  
  For Each c In rng
    If c < 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 3
    ElseIf c > 0 Then
      c.Offset(-2).Resize(4).BorderAround xlContinuous, xlMedium, 4
    Else
[COLOR=#ff0000]      If Not c.Borders.LineStyle = xlNone Then[/COLOR]
[COLOR=#ff0000]        c.Offset(-2).Resize(4).Borders.LineStyle = xlNone
      end if[/COLOR]
    End If
  Next c




End Sub

run time error

object required

 

[Jeffrey Mahoney]

Can you please debug the macro one step at a time and tell me what is the producing the error.