Question description:
This user has given permission to use the problem statement for this
blog.
detect comments in a row and colour corresponding row in column A in yellow if comments exist
Solved by G. B. in 17 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/03/2018 - 08:14
Hello!
User
10/03/2018 - 08:14
hi
User
10/03/2018 - 08:15
basically I want A2
User
10/03/2018 - 08:15
shaded yellow of comment exists
Excelchat Expert
10/03/2018 - 08:15
ok.
User
10/03/2018 - 08:16
B2 to E2
Excelchat Expert
10/03/2018 - 08:16
Just give me few moments to look at it.
User
10/03/2018 - 08:17
ok
Excelchat Expert
10/03/2018 - 08:17
Well. This problem can be resolved by a VBA macro.
User
10/03/2018 - 08:17
ok
User
10/03/2018 - 08:17
sure
Excelchat Expert
10/03/2018 - 08:18
My apologies, but we are not supporting VBA at the moment.
Excelchat Expert
10/03/2018 - 08:18
I can help you by giving a generic code as text here which you can use in your excel work book after customizing it.
User
10/03/2018 - 08:19
ok
Excelchat Expert
10/03/2018 - 08:19
If you know how to use VBA, then I can give that code to you.
User
10/03/2018 - 08:19
yes I know vba
Excelchat Expert
10/03/2018 - 08:20
Great.
Excelchat Expert
10/03/2018 - 08:20
Just give me a minute.
User
10/03/2018 - 08:20
ok
Excelchat Expert
10/03/2018 - 08:23
Sub t()
Dim i As Integer, j As Integer, cmt As Comment
For i = 1 To 5 'row numbers where you want to check
For j = 2 To 5 'column where you want to check
With Cells(i, j)
Set cmt = .Comment
If Not cmt Is Nothing Then
Cells(i, 1).Interior.ColorIndex = 5 '5 is for blue
End If
End With
Next j
Next
Set cmt = Nothing
End Sub
Excelchat Expert
10/03/2018 - 08:23
Please use this code in your sheet and it should work fine. Please make changes where ever necessary.
Excelchat Expert
10/03/2018 - 08:24
I hope that answers your question.
User
10/03/2018 - 08:25
one moment
User
10/03/2018 - 08:26
so how do I only make column A yellow
User
10/03/2018 - 08:26
this formula seems to make the cell with comment yellow
User
10/03/2018 - 08:27
if I am not mistaken
Excelchat Expert
10/03/2018 - 08:27
No, if you see, the cell in row i and column 1 will make it blue. You have to change the color index for yellow.
Excelchat Expert
10/03/2018 - 08:27
for yellow that is 6.
Excelchat Expert
10/03/2018 - 08:28
You have to enter the above code in the respective sheet code page.
User
10/03/2018 - 08:29
ok
User
10/03/2018 - 08:29
but I cant see the reference to column A
Excelchat Expert
10/03/2018 - 08:29
column A is 1 in vba.
User
10/03/2018 - 08:29
ok
User
10/03/2018 - 08:29
got you
Excelchat Expert
10/03/2018 - 08:29
Great :)
Excelchat Expert
10/03/2018 - 08:29
I hope that answers your question.
User
10/03/2018 - 08:31
thanks
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
