Change Worksheet tab color if worksheet contains text

[mnty89]

Hey everyone,

I can't figure this out..

Basically I have a workbook with several sheets. The macro runs and creates new sheets, so at the end I want to put in a small loop that will color code the worksheets that were just added..

I will have worksheets with DataAM, DataPM, DataMID, etc..

So I want to have the vba change the ones that contain "MID" to yellow, "AM" to red, and "PM" to green..

Anyone have any solutions?

Also with an error function built in if needed to where if the sheet is already the color, then skip it.

 

[Caleeco]

Hi mnty,

This is indeed possible with VBA. Would you mind posting your VBA code so I can edit in the bit you require?

Thanks
Caleeco

 

[mnty89]

I'd actually like this to be a standalone piece so I can call it from a few points..

 

[JLGWhiz]

Here is the syntax.

Sub t()
Sheet1.Tab.Color = vbRed
End Sub

 

[Caleeco]

Ok, I can write a standalone macro.

I assume the macro you currently have is naming the sheet also? Also your last requirement, error statement should never trigger (as new sheets are being created no?) Or are the sheets being copied from elsewhere.

Thanks
Caleeco

 

[mnty89]

Not sure why my response didnt post. I actually figured out a stupidly simple solution to just throw these at the end of the loops to do all I need.. Thanks guys for the responses!


Posting here in case someone googles later and needs the syntax

ActiveWorkbook.ActiveSheet.Tab.Color = vbGreen

ActiveWorkbook.ActiveSheet.Tab.Color = vbYellow

ActiveWorkbook.ActiveSheet.Tab.Color = vbRed

 

[JLGWhiz]

Sub t()
If Sheets(DataAM).Tab.Color <> vbRed Then
 Sheets(DataAM).Tab.Color = vbRed
End If
If Sheets(DataPM).Tab.Color <> vbGreen Then
 Sheets(DataPM).Tab.Color = vbGreen
End If
If Sheets(DataMID).Tab.Color <> vbYellow Then
 Sheets(DataMID).Tab.Color = vbYellow
End If
End Sub