# Color on Tab in Excel....

• ### Question

• I need Some Help with these Code.....

Private Sub Worksheet_Change(ByVal Target As Range)
'Hvis Hjemmeholdet fører
If Range("F58") > 8 Then
Me.Tab.ColorIndex = 4
'Hvis Kamp er Uafgjordt
ElseIf Range("F58") = 8 And Range("F59") = 8 Then
Me.Tab.ColorIndex = 6
'Hvis Kamp er Tabt
ElseIf Range("F58") < 8 And Range("F59") > 8 Then
Me.Tab.ColorIndex = 3
ElseIf Range("F58") = 0 And Range("F59") = 0 Then
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

____________________________________________________

This Code WORKS Just FINE But but but...

F58 is Showing The Score From Home Team.  And F59 Show Score For Guest Team

each TEAM  Meat 2 times One Game AS HomeTeam And One Game As GuestTeam

ïs there some how i could get this to Change by a user form to tell If it is a Home Og Guest Sheet to make sure it's Right...

Or any other way

https://www.dropbox.com/s/lz03mkc7am6l6d9/Statistikprogram-28-2014-2015-1-DIVISION.xlsm?dl=0

The Code FOR All Function in This File is "Dart" The Code for Color Tab is in Every Sheet's Where it Should be used

Danish Dart Union

Tuesday, February 10, 2015 8:40 PM

• Re:  Where is the home team at?

I am not quite sure what you want, but I went ahead anyway. <grin>
The code below, should  show a green tab if 'Ringsted 1' won the game and red if they lost etc.
'---
Private Sub Worksheet_Change(ByVal Target As Range)
'Revised February 10, 2015 to use Double variables
Dim dblHome As Double
Dim dblGuest As Double

If Me.Range("S6").Text = "Ringsted 1" Then
dblHome = Me.Range("F58").Value2
dblGuest = Me.Range("F59").Value2
Else
dblHome = Me.Range("F59").Value2
dblGuest = Me.Range("F58").Value2
End If

'Hvis Hjemmeholdet fører
If dblHome > 8 Then
Me.Tab.ColorIndex = 4
'Hvis Kamp er Uafgjordt
ElseIf dblHome = 8 And dblGuest = 8 Then
Me.Tab.ColorIndex = 6
'Hvis Kamp er Tabt
ElseIf dblHome < 8 And dblGuest > 8 Then
Me.Tab.ColorIndex = 3
ElseIf dblHome = 0 And dblGuest = 0 Then
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
'---
FWIW...
"meat" should be "meet"

'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

• Marked as answer by Wednesday, February 11, 2015 4:31 PM
• Edited by Tuesday, November 1, 2016 1:05 AM update link
Wednesday, February 11, 2015 1:26 AM
• Henrik-1,
Re: Who's on First
https://en.wikipedia.org/wiki/Who's_on_First%3F

If you can get each team to put their team name into a particular cell then the Who's at Home question can be resolved.
See possible location (cell BY6) in picture below...

The revised code could be...
'---
Private Sub Worksheet_Change(ByVal Target As Range)
'Revised February 10, 2015 to use Double variables
'Revised February 19, 2015 to use team name from cell BY6
Dim dblHome As Double
Dim dblGuest As Double

'COMMENT:  also try the following code section by switching the top dblHome/dblGuest values _
'                  with the bottom dblHome/dblGuest values...
If Me.Range("S6").Text = Me.Range("BY6").Text Then
dblHome = Me.Range("F59").Value2
dblGuest = Me.Range("F58").Value2
Else
dblHome = Me.Range("F58").Value2
dblGuest = Me.Range("F59").Value2
End If
'END COMMENT SECTION

'Hvis Hjemmeholdet fører
If dblHome > 8 Then
Me.Tab.ColorIndex = 4
'Hvis Kamp er Uafgjordt
ElseIf dblHome = 8 And dblGuest = 8 Then
Me.Tab.ColorIndex = 6
'Hvis Kamp er Tabt
ElseIf dblHome < 8 And dblGuest > 8 Then
Me.Tab.ColorIndex = 3
ElseIf dblHome = 0 And dblGuest = 0 Then
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
'---

Of course, Who's could be on second base and I am just confused. <grin>
'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

• Marked as answer by Sunday, February 22, 2015 1:07 AM
• Edited by Tuesday, November 1, 2016 1:07 AM update link
Thursday, February 19, 2015 5:42 PM
• Re: Almost there

'---
Sub NotThatColorHeSaid()
If Me.Range("AH3") = "" Then
Me.Tab.ColorIndex = xlColorIndexNone
ElseIf Me.Range("S6").Text = Me.Range("AH3").Text Then
'more stuff
End If
End Sub
'---

Or this slight mod on your effort should work...
If Me.Range("AH3") = "" Then
End
Else If Me.Range("S6").Text = Me.Range("AH3").Text Then
'more stuff
'---

Note that in most instances the use of End is not recommended.

'---
Jim Cone
• Marked as answer by Sunday, February 22, 2015 9:45 AM
• Edited by Tuesday, November 1, 2016 1:08 AM
Sunday, February 22, 2015 2:26 AM

### All replies

• Re:  Where is the home team at?

I am not quite sure what you want, but I went ahead anyway. <grin>
The code below, should  show a green tab if 'Ringsted 1' won the game and red if they lost etc.
'---
Private Sub Worksheet_Change(ByVal Target As Range)
'Revised February 10, 2015 to use Double variables
Dim dblHome As Double
Dim dblGuest As Double

If Me.Range("S6").Text = "Ringsted 1" Then
dblHome = Me.Range("F58").Value2
dblGuest = Me.Range("F59").Value2
Else
dblHome = Me.Range("F59").Value2
dblGuest = Me.Range("F58").Value2
End If

'Hvis Hjemmeholdet fører
If dblHome > 8 Then
Me.Tab.ColorIndex = 4
'Hvis Kamp er Uafgjordt
ElseIf dblHome = 8 And dblGuest = 8 Then
Me.Tab.ColorIndex = 6
'Hvis Kamp er Tabt
ElseIf dblHome < 8 And dblGuest > 8 Then
Me.Tab.ColorIndex = 3
ElseIf dblHome = 0 And dblGuest = 0 Then
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
'---
FWIW...
"meat" should be "meet"

'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

• Marked as answer by Wednesday, February 11, 2015 4:31 PM
• Edited by Tuesday, November 1, 2016 1:05 AM update link
Wednesday, February 11, 2015 1:26 AM
• Hi,

Regards

Starain

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Wednesday, February 11, 2015 8:24 AM
• Starain It Seem's That James Cone Have Fix It For Me Because That Works Brilliant

So Thanks Anyway for giving a look at It

Your Regards From Henrik-1 Denmark and Danish Dart Union...

James Cone That Was Wonderfull Great Code.

And Sorry for my Bad Language "Meat" And "Meet" Fast Learner everyday

Thank you VERY much For the Help
Your Regards From Henrik-1 Denmark and Danish Dart Union...

Wednesday, February 11, 2015 4:40 PM
• James Cone.

Is It Possibel To make a Little change in this code you hat made..
the reason is that In this League We are about 100 Dart Club's

In Denmark and that is a Lot of time for me If ill hat to make over 100 Difirents File..

i have try to make it with a Userform where to mark if Ringsted is Home or Out and get value in hidden cell. as 0 For home and 1 For Out For me that Works fine but got some Feetback from other members That THer are to many Function to click on before result...

My option could that be a Compare funktion from 2 cell's one cell is where each Club sign in wich team they are Cell Came From Sheet "Års StateStik" Cell "B2" ,and second cell came when Game Number is given and Name of Home Team Is in Cell "S6"

Hope that was Enough Info So far...
Your Regards Form Henrik-1 Danish Dart Union

Wednesday, February 18, 2015 10:55 PM
• Re:  more changes to the workbook

I do not understand what you are asking.
The Home and Away teams are determined when the Kamp nr (Match No.) is added to a sheet.

'---
Jim Cone

• Edited by Tuesday, November 1, 2016 1:06 AM
Thursday, February 19, 2015 2:24 PM
• Hm That's True James when Kamp nr is add in sheet.

But in Macro we have desided that Only "Ringsted 1" is ok to take change in sheet.

( If Me.Range("S6").Text = "Ringsted 1" Then ) we have 4 teams in Ringsted

ofcause ill Could make 4 file with Change em And rename files for Team 1 - 4

But the problem is as i see it is that we are over 100 Team's In League and its lots of file :-)

it is here i will add a new option "Compare" If Cell S6 is the same As Sheet "års Statestik ("B2"

Henrik-1

Thursday, February 19, 2015 2:40 PM
• Maybe you're going at the problem with the wrong tool or wrong approach. Generally if you do <something> <a bunch of times> and it's all in different files, that's a clue you may want to look at a database solution, preferably something that's available to everyone via a web interface. Passing around hundreds of identical files that will undergo design changes and need to be refreshed is a recipe for no free time and lots of pissed off people. All the code should live in ONE place and everyone should see the same interface. If the code needs to change, you do it in one place and the underlying data is retained. If the data structure needs to be modified, it's easier to modify a constrained set of tables instead of 100 different files.

I'm a lazy developer and the second I do the same thing twice, I figure out a way to do it once...

Thursday, February 19, 2015 2:47 PM
• Hm a Great Ide But i Got this to Work with a Userform when team play at Home then put a "0" in cell and if team play out then "1" in cell and That Works Ok....

But Thanks For your input so far

Henrik-1

Thursday, February 19, 2015 3:04 PM
• Henrik-1,
Re: Who's on First
https://en.wikipedia.org/wiki/Who's_on_First%3F

If you can get each team to put their team name into a particular cell then the Who's at Home question can be resolved.
See possible location (cell BY6) in picture below...

The revised code could be...
'---
Private Sub Worksheet_Change(ByVal Target As Range)
'Revised February 10, 2015 to use Double variables
'Revised February 19, 2015 to use team name from cell BY6
Dim dblHome As Double
Dim dblGuest As Double

'COMMENT:  also try the following code section by switching the top dblHome/dblGuest values _
'                  with the bottom dblHome/dblGuest values...
If Me.Range("S6").Text = Me.Range("BY6").Text Then
dblHome = Me.Range("F59").Value2
dblGuest = Me.Range("F58").Value2
Else
dblHome = Me.Range("F58").Value2
dblGuest = Me.Range("F59").Value2
End If
'END COMMENT SECTION

'Hvis Hjemmeholdet fører
If dblHome > 8 Then
Me.Tab.ColorIndex = 4
'Hvis Kamp er Uafgjordt
ElseIf dblHome = 8 And dblGuest = 8 Then
Me.Tab.ColorIndex = 6
'Hvis Kamp er Tabt
ElseIf dblHome < 8 And dblGuest > 8 Then
Me.Tab.ColorIndex = 3
ElseIf dblHome = 0 And dblGuest = 0 Then
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
'---

Of course, Who's could be on second base and I am just confused. <grin>
'---

Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)
(free & commercial excel add-ins & workbooks)

• Marked as answer by Sunday, February 22, 2015 1:07 AM
• Edited by Tuesday, November 1, 2016 1:07 AM update link
Thursday, February 19, 2015 5:42 PM
• Typical when some thing goes good then someboye else want more...

Is it Posibel to make it that way If No Team Name In (BY6) then No Color at all on Tab

as it is now when no name in (BY6) then it change color fron Winner (green) to Lost game (red)

i try this in code

If Me.Range("AH3") = "" Then End

Else If Me.Range("S6").Text = Me.Range("AH3").Text Then

but got error code so need som help to that

Your regards from Henrik-1 and Danish Dart Union Team's

Sunday, February 22, 2015 1:25 AM
• Re: Almost there

'---
Sub NotThatColorHeSaid()
If Me.Range("AH3") = "" Then
Me.Tab.ColorIndex = xlColorIndexNone
ElseIf Me.Range("S6").Text = Me.Range("AH3").Text Then
'more stuff
End If
End Sub
'---

Or this slight mod on your effort should work...
If Me.Range("AH3") = "" Then
End
Else If Me.Range("S6").Text = Me.Range("AH3").Text Then
'more stuff
'---

Note that in most instances the use of End is not recommended.

'---
Jim Cone
• Marked as answer by Sunday, February 22, 2015 9:45 AM
• Edited by Tuesday, November 1, 2016 1:08 AM
Sunday, February 22, 2015 2:26 AM
• Thanks Alot for that It Works It works

My Regards To you from Denmark

Sunday, February 22, 2015 9:46 AM