VBA not working out

[RealThoughts]

Hi, I am fairly new to excel VBA and I have tried a bunch of different methods. I am trying to highlight only column I and J, if it is not blank, but also trying to highlight the cells with different colors based on values column G. For now, the code that I have inputted makes the excel change different colors while running and end end it with yellow. Any advice is helpful. Thank you.

Sub Color_non_blank_cells()

'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim CallStatus As Object
Set ws = Worksheets("Notifications")
Set ColorRng = ws.Range("I8:J14049")
'Set CallStatus = ws.Range("G8:G14049")


For Each CallStatus In ws.Range("G8:G14049")
    If CallStatus = "Call Later" Then
        ColorRng.Interior.Color = vbGreen
        
    ElseIf CallStatus = "Voice Message" Then
         ColorRng.Interior.Color = vbYellow
         
    ElseIf CallStatus = "Automatic Message" Then
         ColorRng.Interior.Color = vbRed
         
    ElseIf CallStatus = "Will Revert Back" Then
         ColorRng.Interior.Color = vbRed
         
    ElseIf CallStatus = "No Response" Then
        ColorRng.Interior.Color = vbRed
    
    End If
Next CallStatus
'color non blank cells


'On Error Resume Next


'ColorRng.SpecialCells(xlCellTypeConstants).Interior.Color = RGB(87, 214, 42)
'ColorRng.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(87, 214, 42)


End Sub

 

[DanteAmor]

Try this

Sub color_Status()
  Dim ws As Worksheet, rn As Range, wColor As Variant
  Set ws = Worksheets("Notifications")
  For Each rn In ws.Range("G8", ws.Range("G" & Rows.Count).End(xlUp))
    Select Case rn.Value
      Case "Call Later"
        wColor = vbGreen
      Case "Voice Message"
        wColor = vbYellow
      Case "Automatic Message", "Will Revert Back", "No Response"
        wColor = vbRed
      Case Else
        wColor = xlNone
    End Select
    rn.Offset(0, 1).Resize(1, 2).Interior.Color = wColor
  Next
End Sub

 

[RealThoughts]

Thank you so much for the help Dante Amor!

The code is working great. I just have one problem: is there a way I can highlight I and J?
Your code right noe highlights H and I.

Thanks again.

 

[DanteAmor]

Thank you so much for the help Dante Amor!

The code is working great. I just have one problem: is there a way I can highlight I and J?
Your code right noe highlights H and I.

Thanks again.

Sorry for that, update this number:

rn.Offset(0, [SIZE=3][COLOR=#0000ff][B]2[/B][/COLOR][/SIZE]).Resize(1, 2).Interior.Color = wColor

 

[RealThoughts]

Thank you! Works great!

 

[DanteAmor]

Youre welcome