VBA Macro to comment cells according to its value

vabtroni

New Member
Joined
Aug 1, 2017
Messages
32
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! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,321
Office Version
  1. 365
Platform
  1. Windows
You can just use Call Paint
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

vabtroni

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,321
Office Version
  1. 365
Platform
  1. Windows
What is your paint code?
 

vabtroni

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,321
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you sorted it & thanks for the feedback.
 

vabtroni

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,321
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

vabtroni

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,321
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,140
Messages
5,622,949
Members
415,942
Latest member
Data Midwife

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