VBA Macro to comment cells according to its value

Status
Not open for further replies.

vabtroni

New Member
Joined
Aug 1, 2017
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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:

example_1.jpg


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 :) )

example_2.jpg


example_3.jpg


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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Here's the 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(, 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 ;) )
 
Upvote 0
I Made it, Fluff:

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(, 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. :)
 
Upvote 0
Solution
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:

exemplo.jpg


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:

VBA Code:
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 ;) (y)
 
Upvote 0
For the +50 try
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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!

1625049746668.png

1625049976017.png
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top