VBA for if range doesn't equal prescribed ranges.

[imback2nite]

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.

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

 

[Snakehips]

@imback2nite If I understand correctly then try the below.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ci As Integer, fci As Integer
Dim fb As Boolean

If Target.Row < 13 Then Exit Sub

    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
        
            fb = True
            Select Case Target
            Case Worksheets("Tally Sheet").Range("Z3")
            ci = 6:  fci = 1
            
            Case Worksheets("Tally Sheet").Range("Z4")
            ci = 43:  fci = 1
            
            Case Worksheets("Tally Sheet").Range("Z5")
            ci = 54: fb = True: fci = 2
            
            Case Worksheets("Tally Sheet").Range("Z28")
            ci = 12: fb = True: fci = 2
            
            Case Worksheets("Tally Sheet").Range("Z29")
            ci = 33: fb = True: fci = 1
            
            Case Worksheets("Tally Sheet").Range("Z30")
            ci = 53: fb = True: fci = 2
            
            Case Else
            ci = xlNone: fb = False: fci = 1
            
            
            End Select
         Target.Interior.ColorIndex = ci: Target.Font.Bold = fb: Target.Font.ColorIndex = fci
      
        End Select
End Sub

HTH

 

[imback2nite]

@imback2nite If I understand correctly then try the below.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ci As Integer, fci As Integer
Dim fb As Boolean

If Target.Row < 13 Then Exit Sub

    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
       
            fb = True
            Select Case Target
            Case Worksheets("Tally Sheet").Range("Z3")
            ci = 6:  fci = 1
           
            Case Worksheets("Tally Sheet").Range("Z4")
            ci = 43:  fci = 1
           
            Case Worksheets("Tally Sheet").Range("Z5")
            ci = 54: fb = True: fci = 2
           
            Case Worksheets("Tally Sheet").Range("Z28")
            ci = 12: fb = True: fci = 2
           
            Case Worksheets("Tally Sheet").Range("Z29")
            ci = 33: fb = True: fci = 1
           
            Case Worksheets("Tally Sheet").Range("Z30")
            ci = 53: fb = True: fci = 2
           
            Case Else
            ci = xlNone: fb = False: fci = 1
           
           
            End Select
         Target.Interior.ColorIndex = ci: Target.Font.Bold = fb: Target.Font.ColorIndex = fci
     
        End Select
End Sub

HTH

Works like a dream!! Thank you sooo much. I was going to go with Data Validation but it takes up so much room! For 30 pages it was going to go from 6 megabytes to 7.5 megabytes! Sorry it took so long to get back to you but it's just been a crazy week. But thank you once again!