none
Color on Tab in Excel.... RRS feed

  • 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

    Your Regards From Henrik-1
    Danish Dart Union

    Tuesday, February 10, 2015 8:40 PM

Answers

  • 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 Henrik-1 Wednesday, February 11, 2015 4:31 PM
    • Edited by James Cone 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 Henrik-1 Sunday, February 22, 2015 1:07 AM
    • Edited by James Cone 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 Henrik-1 Sunday, February 22, 2015 9:45 AM
    • Edited by James Cone 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 Henrik-1 Wednesday, February 11, 2015 4:31 PM
    • Edited by James Cone Tuesday, November 1, 2016 1:05 AM update link
    Wednesday, February 11, 2015 1:26 AM
  • Hi,

    I am not understand clearly about what your requirement. Please provide the detail information of your requirement.

    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.
    Click HERE to participate the survey.

    Wednesday, February 11, 2015 8:24 AM
    Moderator
  • 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 James Cone 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 Henrik-1 Sunday, February 22, 2015 1:07 AM
    • Edited by James Cone 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 Henrik-1 Sunday, February 22, 2015 9:45 AM
    • Edited by James Cone 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