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]

How about

Sub rdoulaghsingh()
   Dim Fnd As Range
   
   With Sheets("Contents")
      Set Fnd = .Range("A:A").Find(ActiveSheet.Name, , , xlWhole, , , False, , False)
   End With
   If Fnd Is Nothing Then
      MsgBox "Sheet name not found"
      Exit Sub
   End If
   Fnd.Offset(, 2).Value = "In Progress"
   ActiveSheet.Tab.Color = vbYellow
End Sub

 

[rdoulaghsingh]

How about

Sub rdoulaghsingh()
   Dim Fnd As Range
  
   With Sheets("Contents")
      Set Fnd = .Range("A:A").Find(ActiveSheet.Name, , , xlWhole, , , False, , False)
   End With
   If Fnd Is Nothing Then
      MsgBox "Sheet name not found"
      Exit Sub
   End If
   Fnd.Offset(, 2).Value = "In Progress"
   ActiveSheet.Tab.Color = vbYellow
End Sub

That worked! Thank you! I just had to change the Range to Column B. The one thing I didn't realize is that it doesn't manipulate the dropdown list the way I intended it to. I attached a screenshot of what the sheet looks like. I was hoping when it set the text to "In Progress", it would manipulate the dropdown and record the %age change on the chart. I have it set to when the user manually drops down the list and selects one of the statuses, it calculates the percentage of the project completed. The script above does everything except manipulate the dropdown in such a way that it reads it on the chart. Any ideas?

 

[Fluff]

I have it set to when the user manually drops down the list and selects one of the statuses, it calculates the percentage of the project completed.

How?

 

[rdoulaghsingh]

How?

I have the code I sent before (copied below again) in the "CONTENTS" worksheet sub. If the user selects "Not Started" it changes the tab color for the associated action in the contents red and so on depending on the status. I then have a hidden sheet which is calculating the percentage completed based on the dropdown action and writes it to the chart. It's a simple function to count the number of actions on the CONTENTS page from Col D and then assign a value based on "In progress" or "Completed".

Sub Worksheet_Change(ByVal Target As Range)
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
Select Case Target.Value
Case "Not Started": clr = 255
Case "In Progress": clr = 65535
Case "Completed": clr = 3962880
End Select
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

 

[rdoulaghsingh]

How?

Actually...Nevermind! It works! Thank you so much for your help! You're the best!

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[rdoulaghsingh]

Glad it's sorted & thanks for the feedback

Me again. Sorry. Is there any way to have the code work the other way around? Just thinking along the lines if the user manually changes a tab color by right clicking and selecting a color. What I've done in this event is create a refresh button which I hope to read all sheet/tab colors and update the status column cell value depending on sheet name and matching row name. For example, if a user right clicks and manually changes the Introduction 800-171 tab and change the color to vbyellow, then the D3 cell will change to "In Progress" and color fill yellow. Is there any way to accomplish that?

 

[Fluff]

Is there any way to accomplish that?

Nope, at least not very easily.

 

[rdoulaghsingh]

Nope, at least not very easily.

Thank you!