VBA Macro to comment cells according to its value

[vabtroni]

Hello everyone!

I need a VBA code to comment cells on a certain range according to its value, and the content of the comment is given in another table. Please follow the example:

The table on the left is used to assign vehicles to a certain service. The table on the right has remarks about each vehicle. When I press the "UPDATE REMARKS" button, I want it to run a macro that does the following:

1) Clear all previous coments (if any) on range A4:A8
2) On range A4:A8 , comment the vehicle remarks, so that the final work looks like this:

(for the coding, ignore the red circle and arrow, as they are just to show what is to place where )

Thank you all in advance, best regards and stay safe, mates!
Cheers!

 

[Fluff]

You can just use Call Paint

 

[vabtroni]

Here's the 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(, 1).Value
      Next um
      For Each um In Range("B4:B8,B15:B19")
         If Not um.Comment Is Nothing Then
            um.Comment.Delete
         End If
         If .Exists(um.Value) And .Item(um.Value) <> "" Then
            um.AddComment
            um.Comment.Text .Item(um.Value)
            Call paint
         End If
      Next um
   End With
End Sub

After the macro runs, the only painted cell is the one that was selected before I pressed the button, and not the actual cells with the remarks. Should I add something before "Call paint" ? Tried "um.Call Paint" but it returns error (function expected, I guess)

(btw, I replaced "cl" with "um" to better fit my context )

 

[Fluff]

What is your paint code?

 

[vabtroni]

I Made it, Fluff:

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
      Next um
      For Each um In Range("B4:B8,B15:B19")
         If Not um.Comment Is Nothing Then
            um.Comment.Delete
         End If
         If .Exists(um.Value) And .Item(um.Value) <> "" Then
            um.AddComment
            um.Comment.Text .Item(um.Value)
            um.Select
            Call paint
         End If
      Next um
   End With
End Sub

Working perfectly.
Used um.Select and then Call paint

Thank you once again for your help.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[vabtroni]

Hello once again.

I've just gone wild while building the worksheet. Been managing to work around other issues, but I've stalled again.

Having in mind the work and code done so far, I've introduced another column, and based on the value on that column, the color the cells are painted may vary.

Here's the deal:

I have auxiliary macros, working just fine (it was just one on the previous version):
"paint_green" - paints a green thick border
"paint_yellow" - paints a yellow thick border
"paint_red" - paints a red thick border
"clear_paint" - paints a regular black thin border

In the previous version it only painted one color. However, I've introduced on column F different kinds of remarks categories, since minor stuff to more serious stuff. Values on column F are data validated from the values on I4:I6

Also, there's a twist regarding vehicle numbers: the vehicle 2307 can be refered as 2307 or 2357 (plus 50), depending on who's driving it (that is something I sorted out regarding other management needs). The important thing to retain here is that when I refer to 2307 on column E, it will search, comment and paint on column B both 2307 and 2357. You can see it happening with vehicles 2414 and 2337 (wich are their actual fleet numbers), but appear on distribution as 2464 and 2387 (+50), because the drivers assigned to them are from other stations.

Here's the code I'm using, and you can see commented some fixes I've made and also what I need the code to do at that point:

Sub vabtroni()
   Dim um As Range
  
   With CreateObject("scripting.dictionary")
      ' For Each um In Range("E4:E8") - here, it must consider the actual number and also the +50 number
      '   If um.Value <> "" Then .Item(um.Value) = um.Offset(, 2).Value - I've already fixed here the offset to 2 to pick up the correct value for the comment
      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 - I've added this here, so when a comment is deleted, it returns the regular border format to the cell
         End If
         If .Exists(um.Value) And .Item(um.Value) <> "" Then
            um.AddComment
            um.Comment.Text .Item(um.Value)
            um.Select
            ' Here's what I need the code to do, and already tried with no success:
            '   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
         End If
      Next um
   End With
End Sub

Here's where I stalled:

1) Cannot figure out how to refer to the actual fleet number AND its +50
2) Cannot figure out how to refer to value in column F for each "um"

Any help here would be deeply apreciated!

Best regards everyone and stay safe!!
Vasco

 

[Fluff]

For the +50 try

If um.Value <> "" Then
   .Item(um.Value) = um.Offset(, 2).Value
   .Item(um.Value + 50) = um.Offset(, 2).Value
End If

For the colour, you will need to start a new thread as that is a different question.

 

[vabtroni]

Fluff, you rock! Thank you so so so very much.

The +50 issue is solved.

I'll be posting a new thread for the colour issue.

Thank you once again,
Vasco.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Roger96_]

Hi all! I hope you are doing great!

I was looking to this thread and it looks like I need something similar like vabtroni needed, just with some different characteristics.
I also need a VBA code to comment cells on a certain range according to its value just I have two different tables in two different sheets.
I want to have a comment in the cells of the sheet Data with the information of the table in the sheet Info.
Is it possible to create a code where the data looks for the corresponding information on another sheet? For example: in the Data sheet the cell C3 corresponds to Networking and 0%. Is it possible that it looks in the Info table of B1:G1 for the corresponding percentage and in A2:A8 for the corresponding feature? In this case it would be B3 and you will create a comment with this information?

Thank you so much in advance and stay healthy!