Macro to conditionally highlight a tab when a comment is entered into a column

[haibiz9]

Hello,


I have been struggling to write a macro that conditionally highlight a tab when a comment is entered into a column. This macro works just like the conditional formatting feature of Excel. A tab will be automatically highlighted if a comment is entered into a column, and it will be again automatically unhighlighted if the comment is removed by the user.


Your help would be very much appreciated.


I have been looking for help on Excel Forum but it doesn't seem to go anywhere. I have recently learned about Chandoo.org from my superior and decided to seek help here. My apology for cross posting, but I do need help on this macro and the question has been out for a while.


A sample workbook can be found here: http://www.mediafire.com/view/?57j19bklqqouas8


Essentially, on every tab, if a comment or note is placed into the column called 'Company Notes', the tab will be highlighted in yellow. In addition, if another note is entered into any of the columns Client Notes, Requested Element Extension, Requested Element Definition, the tab will be automatically highlighted orange. This formatting will overwrite the Company Notes formatting. Please let me know if you have any questions. FYI, all data are public and nothing is confidential.


I have many workbooks like this to go through and format, so I'm looking for a macro that can be put into my personal workbook and run from there.


I have received a response on Excel Forum with the macro below. However, this one has to be put in "ThisWorkbook" - which is not exactly what I was looking for.


Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)

If Not Intersect(Range("A:A"), Target) Is Nothing Then

ActiveSheet.Tab.ColorIndex = 9

End If

End Sub

 

[SirJB7]

Hi, haibiz9!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I have a few questions that I'd like you to answer:

a) when you speak about comments you're speaking about normal cell contents under certain columns, not comments added to or edited from cells (little red triangle)?

b) how would you identify each related column (Company Notes, Client Notes, REE, RED,...)?

c) by row & col number for each sheet, by cell content, in what row, ...?


Regards!

 

[Hui]

Haibiz9


Firstly, Welcome to the Chandoo.org forums.


Subroutines stored in your Personal workbook cannot access workbook or worksheet events, like the Worksheet_Change event, which is being used by your Code to run.


The secret is having enough code in your file to recognize that a relevant Change has occurred in your file and then call the subroutine in your personal file to do the work.

 

[haibiz9]

Hi SirJB7,


I'm glad to be part of the community. My hope is to learn and contribute to the forum going forward.


My answers for your questions are below:

a) when you speak about comments you're speaking about normal cell contents under certain columns, not comments added to or edited from cells (little red triangle)?

- I meant normal cell contents such as texts, numbers, etc...

b) how would you identify each related column (Company Notes, Client Notes, REE, RED,...)?

- This is what could potentially make the macro complicated. There are no fixed columns for the Company Notes, Clients Notes, REE, and RED. They varies depending on the size of a financial table. I would normally use the search function to locate the columns.

c) by row & col number for each sheet, by cell content, in what row, ...?

- a note could be entered into any cell of a given column. For example, when a note is entered into a cell that belongs to the Company Notes column, the tab will automatically be highlighted yellow. If the note is deleted, the tab will return to normal.

 

[haibiz9]

Hi Hui,


Thank you for your response. I really appreciate it.


I was wondering if there was a way to tweak the codes above and make them accessible from the Personal workbook. That way I can run the macro on multiple workbooks. Once the macro has been executed on a workbook, the conditional formatting will be in place. Any future changes will be detected automatically and the tab will be highlighted accordingly.

 

[haibiz9]

SirJB7 and Hui,


I haven't heard anything back regarding my question so I thought I would check back with you. Please let me know.


Thanks.

 

[SirJB7]

Hi, haibiz9!

Coming back from a glorious non-computer week. Thanks for your point outs regarding a) and c), but still remains b) issue. Without explicitly defining how to identify columns everything what could be done might be in vain. Despite of this, I'll try to come up with a search solution based on patterns which you should adjust as required. Hope to write again in a couple of days.

Regards!

 

[haibiz9]

SirJB7,


Thank you very much for spending your valuable time looking at this macro. I will look forward to hearing back from you.


Thanks.

 

[SirJB7]

Hi, haibiz9!


Sorry for the late reply. Please give a look to this file:

https://dl.dropbox.com/u/60558749/Macro%20to%20conditionally%20highlight%20a%20tab%20when%20a%20comment%20is%20entered%20into%20a%20column%20-%20report_helpneeded%20%28for%20haibiz9%20at%20chandoo.org%29.xlsm


It has a VBA module 'Module1' with the procedure that actually paints a tab, and it's the only part of the stuff that you could place in your personal workbook. This is the code:

-----

[pre]

Option Explicit

Public Sub HighlightTab(piCondition As Integer, psWorkbook As String, psWorksheet As String)
' constants
Const kiNotesNo = 0
Const kiNotesCompany = 1
Const kiNotesClientREERED = 2
Const kiNotesNoColorindex = xlColorIndexNone
Const kiNotesCompanyColor = vbYellow
Const kiNotesClientREEREDColor = vbMagenta 'as no orange natively available
' declarations
' start
' process
With Workbooks(psWorkbook).Worksheets(psWorksheet).Tab
Select Case piCondition
Case kiNotesNo
.ColorIndex = kiNotesNoColorindex
Case kiNotesCompany
.Color = kiNotesCompanyColor
Case kiNotesClientREERED
.Color = kiNotesClientREEREDColor
End Select
End With
' end
End Sub

[/pre]
-----

In 'ThisWorkbook' section, the following code checks if conditions are met and then call previous procedure, and as Hui wrote earlier you should place this code in each workbook as it handles the worksheets change event. This is the code:

-----

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' constants
' key values
Const ksNotesCompany = "Company Notes"
Const ksNotesClient = "Client Notes"
Const ksNotesREE = "Requested Element Extension"
Const ksNotesRED = "Requested Element Definition"
Const kiNotesCompany = 1
Const kiNotesClient = 2
Const kiNotesREE = 3
Const kiNotesRED = 4
' key values count max
Const kiNotesMax = 4
' rows range
Const kiNotesRowMax = 20 'place any proper value
' declarations
Dim iArray() As Integer
Dim I As Long, J As Long
Dim lColumnPrevious As Long, lColumnActual As Long
Dim bCondition As Boolean, iCondition As Integer
' start
' check for one cell change only
If Target.Cells.Count > 1 Then Exit Sub
' process
' clear array
ReDim iArray(kiNotesMax, 2)
' check for key values in rows range
With Sh
bCondition = False
For I = 1 To kiNotesRowMax
' detect last non-empty column
lColumnPrevious = 0
lColumnActual = 1
Do Until lColumnPrevious = lColumnActual And lColumnPrevious <> 0
lColumnPrevious = lColumnActual
lColumnActual = .Cells(I, lColumnPrevious).End(xlToRight).Column
DoEvents
Loop
If .Cells(I, lColumnActual).Value = "" Then
lColumnActual = .Cells(I, lColumnActual).End(xlToLeft).Column
End If
For J = 1 To lColumnActual
' company
If LCase(.Cells(I, J).Value) Like LCase("*" & ksNotesCompany & "*&#34 Then
If iArray(kiNotesCompany, 1) = 0 Then
iArray(kiNotesCompany, 1) = J
iArray(kiNotesCompany, 2) = I
bCondition = True
End If
End If
' client
If LCase(.Cells(I, J).Value) Like LCase("*" & ksNotesClient & "*&#34 Then
If iArray(kiNotesClient, 1) = 0 Then
iArray(kiNotesClient, 1) = J
iArray(kiNotesClient, 2) = I
bCondition = True
End If
End If
' REE
If LCase(.Cells(I, J).Value) Like LCase("*" & ksNotesREE & "*&#34 Then
If iArray(kiNotesREE, 1) = 0 Then
iArray(kiNotesREE, 1) = J
iArray(kiNotesREE, 2) = I
bCondition = True
End If
End If
' RED
If LCase(.Cells(I, J).Value) Like LCase("*" & ksNotesRED & "*&#34 Then
If iArray(kiNotesRED, 1) = 0 Then
iArray(kiNotesRED, 1) = J
iArray(kiNotesRED, 2) = I
bCondition = True
End If
End If
Next J
Next I
End With
' check if change in proper column
If bCondition Then
bCondition = False
iCondition = 0
With Target
For I = 1 To kiNotesMax
If iArray(I, 1) = .Column And iArray(I, 2) < .Row Then
bCondition = True
If .Value = "" Then
iCondition = 0
Else
Select Case I
Case 1
iCondition = 1
Case Is > 1
iCondition = 2
Exit For
End Select
End If
End If
Next I
End With
End If
' end
' paint if condition met
If bCondition Then
HighlightTab iCondition, ActiveWorkbook.Name, Sh.Name
End If
End Sub

-----


Check if this is suitable for you and just advise if any issue.


Regards!

 

[NARAYANK991]

Hi ,


I am not sure I have understood your problem , but can you check out your file here ?


http://img.chandoo.org/narayank/report_helpneeded.xls


Narayan