change tab color based on cell value

[aguilar25]

Hello,

I've tried getting help for this question but now i have changed some cells around and i now need help with this problem, i would like to have a macro that can change a tab color. I have sheets named from 1111 to 1200 and a main sheet named Totals basically what i need is on this sheet in column G (G2:G91) which is now blank to be able to change tabs to red as i enter numbers on the cell such if i enter number example (9578) on G2 then tab 1111 would change to red and so on like G3 for tab 1112 and G4 for 1113.....can this possibly be done?

Thank you

 

[Smitty]

Welcome to the Board!

This assumes that you have the Sheet name in column H:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> strSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("G2:G91")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            strSheet = Target.Offset(, 1).Value<br>                ActiveWorkbook.Sheets(strSheet).Tab.Color = 255<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The code can be tweaked if you don't want to list the corresponding sheet names.

HTH,

 

[aguilar25]

Thank you so much Smitty, this worked great.....THANK YOU SO SO SO MUCH