I have multiple sheets that I want to individually format the tab color. I want the tab to be red if any cell in the D column of that sheet has a red fill.
Now, I've tried it myself, and while I know a good deal in terms of coding, I just can't get it to work. I don't have any code at the moment and will attach the document if need be.
Any and all help is appreciated.
How does one of those cells get a red fill?I want the tab to be red if any cell in the D column of that sheet has a red fill
HTH
Regards, Jeff
I've attached my excel workbook. I don't know if your familiar with the card game Yu-Gi-Oh or not, but my workbook is a collection of my decks.
Column C contains the name of a card.
Column D contains the quantity of that card in the deck. A deck can only contain a max of 3 copies of any card. But there is a list of cards that are deemed "To pwerfull"
and are restricted to 2 or less copies allowed in a deck. SO.....
Column E contains a formula that looks at Column C in the same row, and matches its contents to a Table in a different sheet (named "Limited & Forbidden List")
then returns a number 0-3.
The cells in column D will get a red fill if the cell next to it in column E has number and that number is less than it. Indicating that I have more than the
allowed max quantity of that card in the deck. At which point I want the tab for that sheet to get a red fill, signaling there is a problem with the deck.
The Limited & Forbidden List changes every few months, so having the tab change color would alert me to new problems with each deck when that happens.
try this. uses sheet 3 change event
Maybe try this macro. t present it also colours the "Forbidden list" I need to tweak it a bit to ignore this tab.
Please Login or Register to view this content.
Happy with my advice? Click on the * reputation button below
@Crooza I cant get your code to do anything.
@Jamesera27 When I edit a cell (Click inside the formula bar), and just hit enter, it changes the tabs color. When a cell gets a red fill, it removes the color in the tab.
EDIT: Jamesera27, I got your code working
Formula:
Please Login or Register to view this content.
needed to be changed to
Formula:Please Login or Register to view this content.
Only problem is, for each sheet, I have to manually enter the code.
Last edited by Felgrand89; 03-11-2017 at 11:27 AM.
I had the columns backwards...make sure to put the formula back in e20. I actually tested it by writing in the cell
Last edited by Jamesera27; 03-11-2017 at 11:32 AM.
Ok, so that works, but not in the way I need. Example: If D20 > than E20, then D20 should get a red fill which I then want the tab to turn red.
The way you have it now, E20 has to change to change the tab color.
Using the first macro you gave me, with the edit I posted, works exactly how I want it to. The problem is that every time I make a new tab, I have to manually
add the macro to that tab.
In the attachment, on sheet 3, change the value of D3 to more than 1.
Last edited by Felgrand89; 03-11-2017 at 12:02 PM.
.so maybe instead od a worksheet event, you need a workbook change event..I'm still not sure what it is you change on what sheet to get the red fill. I thought we were only working with the tab on sheet 3. sheet 3 column E has the formula in it...isn't it that column change that triggers the cell to the left to turn red....your original post never mentions adding additional tabs....Just trying to help
Last edited by Jamesera27; 03-11-2017 at 12:35 PM.
Probably, yea. I tried a combination of your code and the code Crooza provided. The problem is while I'm proficient at VB coding, I just don't know how to apply it to Excel.
Last edited by Felgrand89; 03-11-2017 at 12:50 PM.
Probably, yea. I tried a combination of your code and the code Crooza provided. The problem is while I'm proficient at VB coding, I just don't know how to apply it to Excel.
Post #3 explains what I want in more detail. Column E looks at Column C and searches the tables in Sheet1 to return a number(0-3). Then, if E has a number, and D is >, D gets a fill.
Every few months, the tables on sheet1 will change.
A Worksheet_Change event: triggers when you change a cell (or range of cells) value manually or in a macro -- it will not be triggered from a change showing up in a formula or from a change of format. That being said you may need to have a helper column to copy and paste the value of column E into say column F as a value and watch that column for the change.
Did you run the Macro? If you reset all the tabs to white and run the macro ( it's already in the sheet you don't need to copy and paste the code I put above) it should change the appropriate tabs red.Originally Posted by Felgrand89
@Crooza I cant get your code to do anything.
@Jamesera27 When I edit a cell (Click inside the formula bar), and just hit enter, it changes the tabs color. When a cell gets a red fill, it removes the color in the tab.
EDIT: Jamesera27, I got your code working
Formula:Please Login or Register to view this content.
needed to be changed to
Formula:Please Login or Register to view this content.
Only problem is, for each sheet, I have to manually enter the code.
Last edited by Crooza; 03-11-2017 at 06:16 PM.
The only way I can get your code to work is by viewing the code and hitting the "Play Button" icon. And that only changes the tab one way, I want them to also change back to white.
Now, I got this code working:
Formula:Please Login or Register to view this content.
But I have to enter this code in every sheet. Is there a way to use that code from a module for every sheet separately, including sheets that get added?
To give you a better idea of what I want done, download the attached workbook.
In the sheet named "Ghostrick", in cell C20 is the string "One Day of Peace". The number in D20 indicates the number of copies of that card in the deck.
The Number in E20 indicates the maximum number of copies allowed in the deck according to the table in the sheet named "Limited and Forbidden List".
If you change D20 to 2, the cell fills red, indicating that I have more than the allowed copies in the deck, and the tab for that sheet turns red.
Change D20 back to 1, the cell fill no color, and the tab also reverts back to no color.
What Im going for is, every few months, the contents of the tables in Sheet1 change. That change will affect cell E20 in the other sheets, which may or may not
turn cell D20 red thus turning the tab red indicating that I need to alter the deck list. With me so far?
My problem here is, I need to copy the code to each sheet that I want the tab color to change. I can live with that.
But is there a way, with the code not having to be entered in every sheet, to get the above functionality? Did any of that make sense?
So firstly it does work? You shouldn't have to put this in each sheet. I'll address the white tabs when I get back to the computer. You'll need to decide what triggers the code. Do you just want to run it manually or auto trigger in a change event?
this has to be close
Glad to hear it's working...I used some of the code from Crooza and added some of mine and put it into a workbook event. Make sure to close thread and mark solved if it's solved. Add rep to ALL that have helped you...Have a nice day
Last edited by Jamesera27; 03-11-2017 at 08:11 PM.
Felgrand89,
Please edit your posts and use code tags as the forum rules state.
Try this. It runs on the change event n the Forbidden tab
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks