VBA to change cell colour based on content

[Anniema]

Hello, am looking, yet again for assistance. I am trying to have cells change colour based on whether the entry is "1" (Red) or "2" (Green). So far my attempt (macro) has been unsuccessful, I used the following:

Sub FormatData()
Dim ws As Worksheet
Dim cell As Range


Set ws = ThisWorkbook.Sheets("Print for NoticeBoard")

For Each cell In ws.Range("I19:I" & ws.Cells(ws.Rows.Count, "I").End(xlUp).Row)
    If cell.Value = "1" Then
        cell.Interior.Color = RGB(255, 129, 129) ' Red
    ElseIf cell.Value = "2" Then
        cell.Interior.Color = RGB(153, 255, 153) ' Green
    End If
Next cell

End Sub

This did kind of do what I wanted, however as the column "I" (now columns "I&S") in question are fed from other sheets (1 of 7 Mon - Sun) depending on which day of the week is selected in D9). This data is obtained via an "=@indirect" to the relevant cell.

When I changed the day, the input values changed - yay!, however the colours remained as per the initial macro run (a mix of 1s in green and 2s in red) - boo hiss.

Running the macro again did correct the colours to the expected output, however although there were fewer input values than the first attempt, previously highlighted cells remained coloured even with no requisite text present.

What I am asking therefore is can anyone advise how best to achieve the desired result. If this makes any sense.

Snip shows the desired result in column "I" depending on whether the collected data is a 1 or a 2, I susbsequently added column "S" which should do the same as "I" but realised I have insufficient vba skills to write the desired code and trolling the internet failed to provide a solution.

Any and all help greatly appreciated. Thanks

 

[Snakehips]

@Anniema Forgive me if I'm being a bit thick but is this not just a case of needing to use standard Conditional formatting rather than vba?

 

[Anniema]

@Anniema Forgive me if I'm being a bit thick but is this not just a case of needing to use standard Conditional formatting rather than vba?

@Snakehips, no "thick"ness involved as Yes conditional formatting is an option. However as it has the plague like ability of covid to dominate, hog memory, slow down and generally incapacitate any and all spreadsheets it is utilised in (perchance you note I am not a fan?) I am trying to very much avoid it ....hence why the VBA request.

 

[Snakehips]

Thank you for bolstering my self esteem. However, this is without doubt the most overt example of Conditional Formatism I have come across to date.
Does the below help?

Sub FormatData()
Dim ws As Worksheet
Dim cell As Range

Set ws = ThisWorkbook.Sheets("Print for NoticeBoard")
Range("I19:I2000").Interior.Color = xlNone  'Clear sensible expected maximum range of colour?  **Edit end row of range to suit **
For Each cell In ws.Range("I19:I" & ws.Cells(ws.Rows.Count, "I").End(xlUp).Row)

    If cell.Value = "1" Then
        cell.Interior.Color = RGB(255, 129, 129) ' Red
    ElseIf cell.Value = "2" Then
        cell.Interior.Color = RGB(153, 255, 153) ' Green
    End If
Next cell

End Sub