Results 1 to 4 of 4

Thread: VBA Code help

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code help

    I am trying to write a code that changes the color of tabs based on a count if function. The problem I am running into is that for the changes to take place, I have to click on one of the cells and press enter, and then all the tabs change to the appropriate color. I want the tab colors to change progressively as the total gets higher or lower.

    Please see the code below

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Ws1 As Worksheet
    Dim Ws2 As Worksheet
    Dim Ws3 As Worksheet
    Dim Ws4 As Worksheet

    Set Ws1 = Worksheets("1")
    Set Ws2 = Worksheets("2")
    Set Ws3 = Worksheets("3")
    Set Ws4 = Worksheets("4")

    If Range("Risk1").Value >= 5 Then
    Ws1.Tab.ColorIndex = 3
    ElseIf Range("Risk1") >= 3 Then
    Ws1.Tab.ColorIndex = 6
    ElseIf Range("Risk1") <> 0 Then
    Ws1.Tab.ColorIndex = 10
    Else
    Ws1.Tab.ColorIndex = xlColorIndexNone
    End If

    If Range("Risk2").Value >= 5 Then
    Ws2.Tab.ColorIndex = 3
    ElseIf Range("Risk2") >= 3 Then
    Ws2.Tab.ColorIndex = 6
    ElseIf Range("Risk2") <> 0 Then
    Ws2.Tab.ColorIndex = 10
    Else
    Ws2.Tab.ColorIndex = xlColorIndexNone
    End If

    If Range("Risk3").Value >= 5 Then
    Ws3.Tab.ColorIndex = 3
    ElseIf Range("Risk3") >= 3 Then
    Ws3.Tab.ColorIndex = 6
    ElseIf Range("Risk3") <> 0 Then
    Ws3.Tab.ColorIndex = 10
    Else
    Ws3.Tab.ColorIndex = xlColorIndexNone
    End If


    If Range("Risk4").Value >= 5 Then
    Ws4.Tab.ColorIndex = 3
    ElseIf Range("Risk4") >= 3 Then
    Ws4.Tab.ColorIndex = 6
    ElseIf Range("Risk4") <> 0 Then
    Ws4.Tab.ColorIndex = 10
    Else
    Ws4.Tab.ColorIndex = xlColorIndexNone
    End If

    End Sub


    Thank you in advance
    Last edited by jlabrecque; May 16th, 2019 at 09:19 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code help

    Do the named ranges "RiskN" where N= 1,2,3,4 hold formulas or constants that are changed by the user? If the former, consider using a Worksheet_Calculate event rather than Worksheet_Change. If the latter, then you need the Worksheet_Change for the tab colors to change progressively as the total gets higher or lower.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code help

    Hi Joe,

    That was the answer thanks for your help.

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code help

    Quote Originally Posted by jlabrecque View Post
    Hi Joe,

    That was the answer thanks for your help.
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •