integrate tab color change into new sheet/renaming

[minimalVBA]

Hello All,

I'm trying to kill 'two birds with one stone' with my macro. I'm trying to create a dynamic way to make new worksheets with renaming and tab color change. I'm able to create new worksheets with renaming. However, I'm at a lost on how to integrate tab color change. Basic set up is this, Column A has data validation list of 3 places: Home(green), East(yellow) and West(pink). Column B has a list of container information: Container1, Container2 and so forth. Caveat, container names are dynamic and constantly changing day by day. Below is the macro used to create worksheets and renaming it. Looking for help on how to integrate color tab changing into this (if possible)

Code used to create sheets/rename:

Sub newsheetrename()

    Dim rng As Range
    Dim cell As Range
    On Error GoTo Errorhandling

    Set rng = Application.InputBox(Prompt:="Select cell range:", _
        Title:="Create sheets", _
        Default:=Selection.Address, Type:=8)
 
    For Each cell In rng
 
    Sheets.Add(After:=Sheets("Summary")).Name = cell

    Next cell

Errorhandling:

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Are you selecting col B & using that as the sheet name?

 

[minimalVBA]

Correct, column B is my new sheet/rename

 

[Fluff]

Ok, how about

Sub newsheetrename()
   Dim Rng As Range, Cell As Range
   On Error GoTo Errorhandling
   
   Set Rng = Application.InputBox(Prompt:="Select cell range:", _
   Title:="Create sheets", _
   Default:=Selection.Address, Type:=8)
   
   For Each Cell In Rng
      If Not Evaluate("isref('" & Cell.Value & "'!A1)") Then
         Sheets.Add(After:=Sheets("Summary")).Name = Cell.Value
         ActiveSheet.Tab.Color = Cell.Offset(, -1).Interior.Color
      End If
   Next Cell
   
Errorhandling:

End Sub

 

[minimalVBA]

TY that worked! Greatly appreciated

 

[Fluff]

You're welcome & thanks for the feedback.