change a cell value and corresponding sheet/tab color based on value on button click

[rdoulaghsingh]

Hope everyone is having a great weekend! Ok...here we go. I have a button on several sheets in a workbook which is supposed to call one function. The function is supposed to look at a sheet named "CONTENTS" and search column B for matching sheet name. Eg. If the active sheet I'm working on is "Intro" and I click the button on the page, it should search all column B on "CONTENTS" page and find it's matching string "Intro". When it finds the matching string, I need it to move two to the right of the cell with the matching text value and change the cell value 2 rows across to "In Progress" and then change the active sheet tab color with the button to Yellow. I found the code below on the internet which works to change the sheet/tab color of the matching string in the CONTENTS B column, but haven't been able to modify it to a working state to do the above. Any help would be greatly appreciated!

Dim Clr As Long

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
If Target.Value <> "" Then
If Evaluate("isref('" & Target.Offset(, -2).Value & "'!A1)") Then
Target.Value = "In Progress": Clr = 65535
Sheets(CStr(Target.Offset(, -2).Value)).Tab.color = Clr
Else
MsgBox "Sheet " & Target.Offset(, -2).Value & "is not a valid status"
End If
End If
End If
End Sub

 

[Fluff]

My pleasure.

 

[rdoulaghsingh]

Nope, at least not very easily.

What about checking the value of the

Nope, at least not very easily.

So I found a workaround to lock the tabs so users aren't able to right click which solves one part of the puzzle. Using the same code you provided, is it possible to modify the code and assign the macro to a "Reset" button which will check the D column from D3:D60 for a status of "Not Started", "In Progress" or "Completed" and update associated sheet names from column A with sheets/tabs? Just in case you're wondering, this is in the even the user decides to copy and paste a status going down the list. The refresh would help to calibrate the sheet/tab colors based on status. Any help would be greatly appreciated. Thanks in advance.

 

[Fluff]

As that is now a different question, it needs a new thread. Thanks

 

[rdoulaghsingh]

Nope, at least not very easily.

Nope, at least not very easily.

So I found a workaround to disable right clicking the tabs so users won't be able to change the color there - which solves one piece of the puzzle. The next thing I wanted to do is to assign a modification of the code you provided above, assign the macro to a "Refresh" button which will read the value D3:D59 and color the tabs based on associated cell value. For example: It would read the names in B column from B3:B59, taking into consideration a status of "Not Started", "In Progress" or "Completed".

1)If the cells contain "Not Started", the tab with matching name from Column B would change to red.
2)If the cells contain "In Progress", the tab with matching name from Column B would change to yellow.
3)If the cells contain "Completed", the tab with matching name from Column B would change to green.

The reason I'm trying to implement it this way as well is in the even the user decided to copy a status and paste it down the list. If they copy and paste, it doesn't update the sheet color, but if I do the above through a refresh button, that could work. Any help would be greatly appreciated. Thanks!

 

[Fluff]

Please see post#13.

 

[rdoulaghsingh]

As that is now a different question, it needs a new thread. Thanks

Ok. Understood. Thanks!