VBA - Call another macro depending on cell value

[vabtroni]

Hello everyone.

I have the following table:

...and the following code:

Sub vabtroni()
   Dim um As Range
  
   With CreateObject("scripting.dictionary")
      For Each um In Range("E4:E8")
        If um.Value <> "" Then
            .Item(um.Value) = um.Offset(, 2).Value
            .Item(um.Value + 50) = um.Offset(, 2).Value
        End If
      Next um
      For Each um In Range("B4:B8,B12:B16")
         If Not um.Comment Is Nothing Then
            um.Comment.Delete
            um.Select
            Call clear_paint
         End If
         If .Exists(um.Value) And .Item(um.Value) <> "" Then
            um.AddComment
            um.Comment.Text .Item(um.Value)
            um.Select
            ' AND THIS IS WHERE I'M STALLED
         End If
      Next um
   End With
End Sub

Resuming, this code comments cells on range B4:B8,B12:B16 depending on their values, with the remarks of each vehicle referenced on the table on the right. So far, the code runs awesome, thanks to Fluff's help here on MrExcel.

However, I'd like to mark the cells on the range according to the type of remark: green for light stuff, yellow, and red for serious stuff.

I have other macros to help me do that:
- paint_green
- paint_yellow
- paint_red
- clear_paint

At that point on the code where I comented I was stalled, I would like the code to do the following:

in each row/"um":
if value on column F = Range("I4").Value Then Call paint_green
if value on column F = Range("I5").Value Then Call paint_yellow
if value on column F = Range("I6").Value Then Call paint_red

Thanks in advance for any help on this, best regards and stay safe,
Vasco.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
Could you post your final code?

 

[vabtroni]

Sure. Here it goes:

Sub vabtroni()
   Dim um As Range
   Dim d As Object
   
   Set d = CreateObject("scripting.dictionary")
   
   With CreateObject("scripting.dictionary")
      For Each um In Range("E4:E8")
        If um.Value <> "" Then
            .Item(um.Value) = um.Offset(, 2).Value
            .Item(um.Value + 50) = um.Offset(, 2).Value
            d.Item(um.Value) = um.Offset(, 1).Value
            d.Item(um.Value + 50) = um.Offset(, 1).Value
        End If
      Next um
      For Each um In Range("B4:B8,B12:B16")
         If Not um.Comment Is Nothing Then
            um.Comment.Delete
            um.Select
            Call clear_paint
         End If
         If .Exists(um.Value) And .Item(um.Value) <> "" Then
            um.AddComment
            um.Comment.Text .Item(um.Value)
            um.Select
            Select Case d.Item(um.Value)
                Case Range("I4").Value
                    Call paint_green
                Case Range("I5").Value
                    Call paint_yellow
                Case Range("I6").Value
                    Call paint_red
            End Select
         End If
      Next um
   End With
End Sub

 

[Akuini]

Good