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

#### rdoulaghsingh

##### Board Regular
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

My pleasure.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### rdoulaghsingh

##### Board Regular
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

##### MrExcel MVP, Moderator
As that is now a different question, it needs a new thread. Thanks

#### rdoulaghsingh

##### Board Regular
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!

#### rdoulaghsingh

##### Board Regular
As that is now a different question, it needs a new thread. Thanks
Ok. Understood. Thanks!

Replies
3
Views
337
Replies
3
Views
170
Replies
7
Views
240
Replies
7
Views
130
Replies
11
Views
220

1,191,397
Messages
5,986,359
Members
440,020
Latest member
IfsandSums

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back