simple tab color change based on any entry of any sheet

[esrv303]

I have multiple sheets with various names. User would like tab color to change to (example:red) if ANY entry is made to any cell on that sheet. I have no code experience with excel....thanks

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in any cell and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Tab.Color = vbRed
End Sub

Let me know if you want this action to apply to all your sheets.

 

[esrv303]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make an entry in any cell and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Tab.Color = vbRed
End Sub

Let me know if you want this action to apply to all your sheets.

Have not tried yet, but yes, needs to apply to all sheets....thanks so much

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Make an entry in any cell in any sheet and press the RETURN key.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ActiveSheet.Tab.Color = vbRed
End Sub

 

[esrv303]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Make an entry in any cell in any sheet and press the RETURN key.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ActiveSheet.Tab.Color = vbRed
End Sub

That appears to work great. Not to get too deep in the weeds but I assume I need to add this code to my blank template sheet? Because if I clear an entry and that sheet is blank again, the tab doesn't turn back to no color.....but thank you so much, I'll play with it. Love this forum so far

 

[mumps]

Replace the previous code with this version and see if it does what you want:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If WorksheetFunction.CountA(ActiveSheet.UsedRange.Cells) > 0 Then
        ActiveSheet.Tab.Color = vbRed
    Else
        ActiveSheet.Tab.Color = xlNone
    End If
End Sub

 

[esrv303]

Replace the previous code with this version and see if it does what you want:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If WorksheetFunction.CountA(ActiveSheet.UsedRange.Cells) > 0 Then
        ActiveSheet.Tab.Color = vbRed
    Else
        ActiveSheet.Tab.Color = xlNone
    End If
End Sub

Thanks....used new code and saved....went to random sheet....entered the word "test".....tab turned red.....then cleared contents on the word "test" I entered, but didn't change tab back to no color

 

[mumps]

if I clear an entry and that sheet is blank again

When you cleared the contents of the cell, was the sheet blank?

 

[esrv303]

When you cleared the contents of the cell, was the sheet blank?

Ahh, I see where you are going I think. All my sheets have beginning data in them, the user then enters quantities and other notes....so guess I should have stated that when I clear contents, I'm just clearing changes made to the original setup of the sheet/sheets

 

[mumps]

Do you want the tab colour removed if you clear any cell?