Excel VBA to Add New Sheet then Change Name to Current Date and Tab Color

[astrid22]

Hi, I am relatively new to Excel VBA and I am trying to create a personal template for my work. I already have the below code to insert a new sheet. But I don't know how to change the tab color based on a fixed cell value.
Sub GenerateConf()
On Error GoTo MyError
Sheets.Add
ActiveSheet.Name = WorksheetFunction.Text(Now(), "mm.dd.yyyy" + " OA ")
Exit Sub
MyError:
MsgBox "There is already a sheet called that."
End Sub

Cell J22 contains the letters OA. I want to be able to insert a new sheet, rename it to the current date + the letters OA + the count of sheets with OA as part of its name on the same day (like (date today) OA 1; (date today) OA 2; (date today) OA 3, etc) and then change the tab color to light green.

Any ideas?

 

[Peter_SSs]

@Peter_SSs,

How would your code change as every day the tab has another color?
Obviously the tabs created on the same day have the same color.
Thank you.

That would depend on what colours or groups of colours you wanted to use. There would need to be a list or some logical process to decide on the colour to use when a new day started.

 

[Ingolf]

Sheets(Sheets.Count).Tab.ColorIndex = Day(Date) + 2

Thank you.

 

[Ingolf]

That would depend on what colours or groups of colours you wanted to use. There would need to be a list or some logical process to decide on the colour to use when a new day started.

Thanks for reply.
If is possible, in sheet2 are, starting A2, date in order for that current month (or year?) and in column B will put color we need, (or generated by code).
It will be great if possible.

Thank you.

 

[Peter_SSs]

T.. in sheet2 are, starting A2, date in order for that current month ... and in column B will put color we need..

Assuming you mean that column B contains an appropriate ColorIndex number for that day, you could just modify the last line of my sub as follows.

Sheets(Sheets.Count).Tab.ColorIndex = Sheets("Sheet2").Range("B2:B32").Cells(Day(Date)).Value

 

[Ingolf]

Thank you so much!!!