Make cells negative based upon the value of another-CodePudding

I'm able to colour cells based upon the value of another with the code below, which I found on this forum

Sub ColorMeElmo()
   Dim i As Long, r1 As Range, r2 As Range

   For i = 2 To 100
      Set r1 = Range("D" & i)
      Set r2 = Range("A" & i & ":C" & i)
      If r1.Value = "Aankoop" Then r2.Interior.Color = vbRed
      If r1.Value = "Verkoop" Then r2.Interior.Color = vbBlue
      If r1.Value = "Dividend" Then r2.Interior.Color = vbYellow
   Next i
End Sub

Now I'm trying to make cells negative or possitive based upon the value of r1. I tried this:

If r1.Value = "Verkoop" Then r2.Value = r2.Value * (-1)

CodePudding user response：

Issue seems to be that r2 contains multiple cells hence below code will not work

If r1.Value = "Verkoop" Then r2.Value = r2.Value * (-1)

This needs to be changed to

If r1.Value = "Verkoop" Then 
 For Each c In r2.Cells 
     c.Value = c.Value * (-1)
 Next
End if

CodePudding user response：

Ibrez’s answer is right. Anyway, I would recommend you write some clearer and more performant code (having 3 conditionals in each loop could be kind of slow if you have a big data sheet). This is my suggestion:

Public Sub ColorMeElmo()
   Dim i As Long, r1 As Range, r2 As Range, r3 As Range
   Dim color As Long
   Dim value2 As Integer, value3 As Integer

   For i = 2 To 100
      Set r1 = Range("A" & i)
      Set r2 = Range("B" & i)
      Set r3 = Range("C" & i)
      
      Select Case r1
          Case "Aankoop"
              color = vbRed
              value2 = r2
              value3 = r3
          Case "Verkoop"
              color = vbBlue
              value2 = -r2
              value3 = -r3
          Case "Dividend"
              color = vbYellow
              value2 = r2
              value3 = r3
          Case Else
              'some unexpected value would be highlighted in grey
              color = RGB(127, 127, 127)
              value2 = r2
              value3 = r3
      End Select
      
      r2.Interior.color = color
      r2 = value2
      r3.Interior.color = color
      r3 = value3
   Next i
End Sub