PCC2019
New Member
- Joined
- Jun 7, 2017
- Messages
- 7
I have a code that I have written to change the tab color of that particular worksheet if information was written in that worksheet but I have found a bug and I was hoping that someone here could look at the code and help me work this out. The code works beautifully except for one small detail... Basically if I enter information into a worksheet and tab to the next cell the code does what I intended it to do and the tab turns red indicating that this sheet has information and needs to be printed at end of day reporting. However, if I enter information into a cell and don't tab to the next cell and instead click on a different worksheet tab then the worksheet tab I just clicked on will turn red instead of the worksheet tab that has the information. I have been careful in my process as to not have the wrong sheet turn red but mistakes happen and I would like to eliminate the chance of this error in the future. Any help would be much appreciated. Thank you in advance!
below is the code I've written:
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("A55").Text
With ActiveSheet.Tab
Select Case MyVal
Case "1"
.Color = vbRed
Case Else
.Color = RGB(0, 176, 240)
End Select
End With
End Sub
below is the code I've written:
Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("A55").Text
With ActiveSheet.Tab
Select Case MyVal
Case "1"
.Color = vbRed
Case Else
.Color = RGB(0, 176, 240)
End Select
End With
End Sub