Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Location
    MA
    Posts
    2

    Unanswered: Contitionally formatting tabs!

    Is there a way i can have excel change the color of a tab if, lets say cell b1 has a value of less then 50? I am not too familiar with Macros so if you give me a code, can you tell me how to use it?

    Thank you in advance

  2. #2
    Join Date
    Aug 2010
    Location
    NYC
    Posts
    10

    lopisan affembly

    This is the code to set the tab's color based on the value of cell B1.

    Public Sub SetSheetColor()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    If Range("B1").Value > 50 Then
    ws.Tab.ColorIndex = 30
    Else
    ws.Tab.ColorIndex = 1
    End If
    End Sub

    This function calls SetSheetColor whenever cell B1 is changed:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") Then SetSheetColor
    End Sub
    --------------------------------------------------
    Matt
    Case Culture - Fashion Cases for iPhone and BlackBerry

  3. #3
    Join Date
    Sep 2010
    Location
    MA
    Posts
    2
    Quote Originally Posted by PriorityParts View Post
    This is the code to set the tab's color based on the value of cell B1.

    Public Sub SetSheetColor()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    If Range("B1").Value > 50 Then
    ws.Tab.ColorIndex = 30
    Else
    ws.Tab.ColorIndex = 1
    End If
    End Sub

    This function calls SetSheetColor whenever cell B1 is changed:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B1") Then SetSheetColor
    End Sub
    How do i apply this to my sheet. Is it a matter of copy paste? i'm sorry im not very advanced in excel.

  4. #4
    Join Date
    May 2009
    Posts
    6
    Go to Tools -> Macro -> Visual Basic Editor.

    Paste it into a new screen when you click on 'This Workbook' under Microsoft Excel Objects and Save. When you hit the 'run' button at the top of the Visual Basic GUI, it will change the color to black or red based off of the value in B1.

    Awesome post, Priority!

Posting Permissions

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