VBA - Call another macro depending on cell value

vabtroni

New Member
Joined
Aug 1, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hello everyone.

I have the following table:
exemplo.jpg


...and the following code:

VBA 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

Well-known Member
Joined
Feb 1, 2016
Messages
2,955
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
Could you post your final code?
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

vabtroni

New Member
Joined
Aug 1, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Sure. Here it goes:

VBA Code:
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

;)(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top