0

First attempt got closed as duplicate and I've used the suggested thread (VBA code doesn't run when cell is changed by a formula) & I've done some changes, but it still isn't working nothing happens.

I would like to change the color of a sheet tab based on the value of a cell (that has a formula in it) in a different sheet, but same workbook. The Different Sheet is the Master sheet "Test Summaries", and the cell I'd like to refer to is different for each sheet.

What I need:

  • Tab "Microscopy" to change either color to red/orange/green based on the formula in Sheet "Test Summaries" cell"C2" (in this case Red) Tab
  • "Culture" to change either color to red/orange/green based on the formula in Sheet "Test Summaries" cell "C3" (in this case Red)
  • ...and so on

In the picture you'll see that I've got a formula that tells me what color the tab should be based on specific testing criteria. I've tried the following code, but it won't change the color of the tab unless the cell contains text and the macro only allows me to refer to one cell.

My updated code that is in the Sheet "Microscopy" object.

    Private Sub Worksheet_Calculate1(ByVal Target As Range)
Dim MyVal$
MyVal = ActiveWorkbook.Sheets("Test Summaries").Range("C2")

With ActiveSheet.Tab
    Select Case MyVal
        Case "RED"
            .Color = vbRed
       Case "GREEN"
            .Color = vbGreen
        Case "AMBER"
            .ColorIndex = 45

    End Select
End With
End Sub

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> PrevVal Then
        MsgBox "Value Changed"
        PrevVal = Range("A1").Value
    End If
End Sub

But this didn't work (nothing happens), so then I added the following based on thread I've made some changes to my code, but nothing happens.

This code into ThisWorkbook Objects (but I don't understand what it's suppose to do, but it doesn't seem right as I need each tab to refer to a different cell)

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("C3").Value
End Sub

Then in the Modules 1 I've put this

Private Sub Workbook_Open()
    PrevVal = Sheet1.Range("C3").Value
End Sub

I would really appreciate help. btw I'm working in excel 2010

braX
  • 11,506
  • 5
  • 20
  • 33
Cutlery
  • 5
  • 4
  • The `Worksheet_Calculate` event handler does not have `ByVal Target As Range`, as you can see in the original linked duplicate. – BigBen May 27 '20 at 16:48
  • I must admit, I have a beginners knowledge of vba so would really appreciate if you could explain a bit more. – Cutlery May 27 '20 at 16:50
  • You can only have one `Worksheet_Calculate` code block in a sheet module. That `Private Sub Worksheet_Calculate1(ByVal Target As Range)` won't do anything. You need to have all the relevant code for changing the tab color inside the `Worksheet_Calculate` handler. – BigBen May 27 '20 at 16:51
  • Okay, I think that makes sense. Then why was the thread you linked previously suggesting to add the other Subs in to the ThisWorkBook Objects and Modules? I really don't understand this, I found it so confusing, as you can probably tell – Cutlery May 27 '20 at 16:55
  • All the other code is basically just to keep track of the previous value (i.e. the value of the formula before it recalculated). That way you can tell when the calculated value has changed. – BigBen May 27 '20 at 17:00
  • Ok, but how would the code track multiple changes? I've got about 50 tabs would need to change color based on a specific cell each. I've removed the ByVal Targe as Range, but still nothing happens. not even an error. – Cutlery May 27 '20 at 17:07
  • You might just skip tracking the previous values - to be honest, if that tab doesn't have many other formulas or calculations, you can just use the `Worksheet_Calculate` event by itself. – BigBen May 27 '20 at 17:08
  • So now I have this as my only code in the workbook, in the sheet's code, but nothing is happening- am I suppose to execute it in some way? 'Private Sub Worksheet_Calculate1() Dim MyVal$ MyVal = ActiveWorkbook.Sheets("Test Summaries").Range("C2") With ActiveSheet.Tab Select Case MyVal Case "RED" .Color = vbRed Case "GREEN" .Color = vbGreen Case "AMBER" .ColorIndex = 45 End Select End With End Sub' – Cutlery May 27 '20 at 17:12
  • Remove the `1` at the end of `Private Sub Worksheet_Calculate1`. – BigBen May 27 '20 at 17:14
  • Done, but still there is no colour change, I've even saved, closed and reopened the file. – Cutlery May 27 '20 at 17:20

1 Answers1

0

Something like the following should do the trick, looping through the list of worksheet names in column A and using Offset to refer to their corresponding formula in column C.

Private Sub Worksheet_Calculate()
    Dim rng As Range
    For Each rng In Me.Range("A2:A5") ' change as needed
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets(rng.Value)

        Select Case rng.Offset(, 2).Value
            Case "RED"
                ws.Tab.Color = vbRed
            Case "GREEN"
                ws.Tab.Color = vbGreen
            Case "AMBER"
                ws.Tab.ColorIndex = 45
        End Select
    Next
End Sub

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Thanks @BigBen , Where do I put this code? I've added this to the ThisWorkbook Under Objects, but nothings happened. Then I've put it as a Module, still nothing. Then I've put it into each sheet, still nothing. I can see that you've gotten the code to work, but it isn't doing it for me. Suggestions? – Cutlery May 28 '20 at 10:38
  • This only goes in the 'Test Summaries' sheet code module. – BigBen May 28 '20 at 11:57
  • It did something- an Error RunTime Error '9' then this was highlighted `Set ws = ThisWorkbook.Worksheets(rng.Value)` – Cutlery May 28 '20 at 12:32
  • That means that your workbook does not have a worksheet named whatever is in that `rng`. – BigBen May 28 '20 at 12:35