imback2nite
Board Regular
- Joined
- Oct 30, 2004
- Messages
- 208
- Office Version
- 2003 or older
- Platform
- Windows
I'm using this code in Excel 2019. I used to use it in Excel 2007 and it worked fine. What is happening now is if I put a character that equals the range Worksheets("Tally Sheet").Range("Z3") in page 16 it works fine. But if I put in a character that after that that does NOT equal Worksheets("Tally Sheet").Range("Z3") the format remains. That's why I included the last line If Target = Worksheets("Tally Sheet").Range("Z31") then no color because range WOrksheets('Tally Sheet").Range("Z31") was blank. I would like to get rid of the last line and add something that will leave the cell colorless and font.ColorIndex = 1. And if at all possible can I get this to work below row 12. I know this is asking alot but I would really appreciate the help.
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case ActiveSheet.Index
Case 14 To 44
If Target.Cells.Count > 1 Or IsEmpty(Target) Then: Target.Interior.ColorIndex = xlNone: Target.Font.Bold = False: Exit Sub
If Target = Worksheets("Tally Sheet").Range("Z3") Then: Target.Interior.ColorIndex = 6: Target.Font.Bold = True: Target.Font.ColorIndex = 1
If Target = Worksheets("Tally Sheet").Range("Z4") Then: Target.Interior.ColorIndex = 43: Target.Font.Bold = True: Target.Font.ColorIndex = 1
If Target = Worksheets("Tally Sheet").Range("Z5") Then: Target.Interior.ColorIndex = 54: Target.Font.Bold = True: Target.Font.ColorIndex = 2
If Target = Worksheets("Tally Sheet").Range("Z28") Then: Target.Interior.ColorIndex = 12: Target.Font.Bold = True: Target.Font.ColorIndex = 2
If Target = Worksheets("Tally Sheet").Range("Z29") Then: Target.Interior.ColorIndex = 33: Target.Font.Bold = True: Target.Font.ColorIndex = 1
If Target = Worksheets("Tally Sheet").Range("Z30") Then: Target.Interior.ColorIndex = 53: Target.Font.Bold = True: Target.Font.ColorIndex = 2
If Target = Worksheets("Tally Sheet").Range("Z31") Then: Target.Interior.ColorIndex = xlNone: Target.Font.Bold = False: Target.Font.ColorIndex = 1
End Select
End Sub