Conditional formatting to color a tab when a comment is entered into a cell of a column

[haibiz9]

I was having difficulties writing a macro to conditionally highlight a tab with a specific color when users write a comment into any cell of a column. I would greatly appreciate if you could help.

Thanks.

[MarkinTX]

Try something like this, where 1 is column A 2 is B etc.

Please Login or Register  to view this content.

<------If this helped you please click the star!

[MarkinTX]

To insert that code right click on the tab you want to insert the code and click "View Code"
Then paste the code above.



<------If this helped you please click the star!

[haibiz9]

MarkinTX,

Thank you for your response. I really appreciate it.

Unfortunately the macro you suggested wasn't the one that I was looking for. I guess I wasn't very clear on the original post. Essentially what I want is a macro that works just like the conditional formatting function of Excel. If a user enter a note in any cells of a specific column, the tab will be automatically highlighted. Otherwise, nothing will happen.

Thanks.

[haibiz9]

Just to further clarify my point above. I don't want a macro that is executed after the fact. I want something that works just like the conditional formatting feature of Excel where the application automatically performs a task when a certain condition is met. For example, when a comment is entered into a column, the tab will be automatically highlighted. If that comment is removed, the program will again automatically unhighlight the tab.

[Mowgli]

Try modifying Martins code to the following, he got it working fine however it relies on the data entry to stay within the same column, i.e. type data and press enter as opposed to tab

Please Login or Register  to view this content.

[Mowgli]

Or a better option may be the following, it will detect any time there is a change in Column A and change the Tab colour accordingly

Please Login or Register  to view this content.

[haibiz9]

Mowgli,

Thank you very much for your input. I've tried putting the macro into one of the modules in my personal workbook, but I couldn't figure out how to run it. The macro name wouldn't show up on the Macros window. Please help. I'm just a newbie trying to learn Excel macro. Your help is much appreciated.

[Mowgli]

Oops,

It also had a spelling mistake so can understand it not working so well. My computer had crashed so I went onto the iPhone to type in a reply for you (Excuses excuses eh? ). The correct code should be

Please Login or Register  to view this content.

If you place this in the "ThisWorkbook" section instead of a module then it should work ok. If not feel free to upload your workbook and I will add the code.

There will be no macro to call as it automatically runs and changes the tab colour to whichever colour you select in the code as a value for ColorIndex (in this case 9 which is red) every time it detects a change to the contents of a column you select in the code (in this case A). This is a great place to learn as everything I have learnt has pretty much come from this forum

[haibiz9]

You're awesome, Mowgli! Sorry about the crash. It might have taken you a while to type in using iPhone huh?!

The reason why I had to place it in a module was because my colleagues and I had to format a lot of workbooks and it will take a very long time to put the codes in every single one of them. In addition, not everyone is familiar with macros.

I've attached the workbook. Essentially, on every tab, if a comment is placed into the column called 'Company Notes', the tab will be highlighted in yellow. In addition, once a note is entered into the columns Client Notes, Requested Element Extension, Requested Element Definition, the tab will be automatically highlighted orange as well. This formatting will overwrite the Company Notes formatting. Let me know if this isn't clear to you.

report_helpneeded.xls

A zillion thanks!

[haibiz9]

Mowgli and the excel experts,

My question has been out for a while and I haven't found the answer yet. Any help would be greatly appreciated.

Thanks.