Change Active sheet color when data is changed

[usafmeinweg]

Hello all,

I have a workbook with Several sheets with custom names. What I would like to do is create VBA that when data in the active sheet is changed (A2:A20), it will change the color of the sheet tab to red (255). I need this to work for all the sheets in the workbook. Can you help me out?

Thanks in advance

[watersev]

see attachment, try to change data in range [a2:a20], changes tab color to red

[usafmeinweg]

watersev,
That worked like a charm. What if I now want any cell within the worksheet changed (not just A2:A20).

OR

I have several worksheets named LastName 3132 and LastName 1098

Worksheets LastName 3132- if data in cells A2:A20 changes, then sheet color changes to red
Worksheets LastName 1098- if data in cells D5:D98 cahnges, then sheet color changes to red

[watersev]

see attachament. To see the code (it's very simple): open attachment - Alt+F11 - Ctrl+R - find ThisWorkbook tab under the file name header section and double click it.
Or open attachment and right-click Excel icon near File menu - Original code - that will take you to the workbook code page.

[usafmeinweg]

On the right track. I have multiple sheets LastName 3132, LastName 1098 "LastName" is variable
ie.
Doe 3132, Doe 1098
Smith 3132, Smith 1098
(this goes on for about 30 guys)

In your example, it works only if the sheetname is "LastName 3132". I would like for the code to see that 3132 is in the sheetname and it to apply color to the tab when A2:A20 is changed. And see that 1098 is in the sheetname and it to apply color to the tab when D5:D98 is changed

I appreciate your help

[watersev]

in the last attached example colouring works for sheets LastName 3132 and LastName 1098 as per different ranges specified

[usafmeinweg]

I got that working, but "LastName 3132" and " LastName 1098" is actually some guys last name ie: Smith 3132, Smith 1098

There are atleast 30 individuals names. so I don't want to rewrite the code to have

Please Login or Register  to view this content.

I would have to do that for all 30+ guys and teach people in my office how to modify the code when they add a new sheet.

If I can get the code to see that 1098 is in the sheetname then it applies the correct formatting according to the cells that were modified.
AND
If the sheet updated contained 3132 in the name, then it would apply that formatting for the other range of values updated.

[watersev]

if you can create tabs structure with controled range like in this file (1 sheet [a2:a20], 2 sheet [d5:d98], 3 sheet [a2:a20] etc.) then you can add new sheets to the attached book with any names and it will work on them automatically. Otherwise you'll need to name all your sheets in the code as per example

[usafmeinweg]

That worked beautifully. Thank you