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.

 

[vabtroni]

I tried to insert the following on the code, right up there where I said I was stalled:

            If um.Offset(, 1).Value = ActiveSheet.Range("I4").Value Then Call paint_green
            If um.Offset(, 1).Value = ActiveSheet.Range("I5").Value Then Call paint_yellow
            If um.Offset(, 1).Value = ActiveSheet.Range("I6").Value Then Call paint_red

It makes sense to me, but I am kinda n00b on VBA, I understand the logic but I lack the synthax. Well, the macro run with no errors, but completely ignored these conditions and acted as if these lines weren't there.

Am I missing something?

Cheers,
V.

 

[Akuini]

Hi, @vabtroni
I don't understand this part:

      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

If you want to populate col F (Type) then the blue lines should be:
um.Offset(, 1).Value

Then you can use the dictionary, like this:
This is untested.

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(, 1).Value
            .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)
            Select Case .Item(um.Value)
                Case "Minor"
                    Call paint_green
                Case "Caution"
                    Call paint_yellow
                Case "Major"
                    Call paint_red
            End Select
            ' AND THIS IS WHERE I'M STALLED
         End If
      Next um
   End With
End Sub

Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.

 

[vabtroni]

Hi Akuini, thanks for your reply

Right now I'm on mobile, don't have the file here.

However, I'll give you the link where this code showed up first, I'm pretty sure you'll understand there why the offset must be 2 and why I created this new thread, just follow the thread as the code was evolving. The main goal is to insert the remarks as coment for each vehicle.

This is the link: VBA Macro to comment cells according to its value

Cheers,
V.

 

[Akuini]

I'll wait for the sample file.

 

[vabtroni]

Akuini:

http://www.troni.pt/test.xlsm

The module 1 in VBA has the macro I'm talking about.

The module 2 has the aux macros to paint the borders.

It is important that you have checked out previously the thread I talked about, for you to understand what this macro is supposed to do

Thank you in advance for your help.

 

[vabtroni]

If you want to populate col F (Type) then the blue lines should be:

It should populate col G to pick up the correct text to comment.
Is it possible also to populate col F to meet the condition on how the commented cell should be painted?

 

[Akuini]

You need another dictionary to populate F column, like this:

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
' --------------------------------------------------------------------------------
' This piece of code is just being ignored and doing nothing.
' It was supposed to call the paint macros depending on the value of cell in column F
' I believe I didn't get something and the condition is not being met by the parameters I wished to
            
            
            Select Case d.Item(um.Value)
                Case "Minor"
                    Call paint_green
                Case "Caution"
                    Call paint_yellow
                Case "Major"
                    Call paint_red
            End Select
' --------------------------------------------------------------------------------
         End If
      Next um
   End With
End Sub

 

[vabtroni]

Thank you very very much!

Will try it soon.

However, I still have one last question. When you state Case "Minor" , Case "Caution" and Case "Major", can I relate to the cells value I4 I5 and I6 instead of relating it to the values itself? Just saying it because in the table on the right, the column F is data validation from range "I4:I6". Would it work if I stated it this way: Case Range("I4").Value etc etc?

 

[vabtroni]

Just tested, and it works perfectly. Both your solution and my final fix.

Thank you so very much, Akuini

Cheers everyone and stay safe ?