Conditional formatting in vba with multiple variables as ranges

Hi,

Have formulae to collect the data from it's raw data source then display it in an alternative layout with visual keys created using conditional formatting. Issue is it's too clunky on the machines here so thinking vba that working on button click and only looks for new information to add/ conditionally format each time may be less clunky?

Attached is the Raw Data and it is summary 2 I am looking to do using VBA. At the moment it pulls the data from the raw data table into the format, then in conditional formatting indexes the Site and Date against the raw table to pick up the rating and colour the check date cell accordingly.

My VBA skills are still early days so please don't laugh :-) I had a go at starting to write something (*see below) that would do the conditional formatting of the whole sheet at once each time, but realised that the method I was attempting would need to loop through two variables which I am not sure how to do. To be fair, my script probably wouldn't work even if I knew how to solve that issue.

Notes:
My actual data is set up so that Raw data table is on one tab and the Summary 2 table is on another in the same workbook. I was planning to build the code against the Summary 2 tab with a button on the sheet to run the code. (My attachment currently has all the information on the same tab)

Initially I was building the script just to enhance the performance of the conditional formatting, but I worry the issue might happen against the whole process, so it would be good to have the script pick up new data from the raw data table and insert it onto Summary 2, then conditionally format the newly filled in (contains data but with white background) cells accordingly.

In order for the script to know if it is new data, if it needs to mark in another column on the Raw data table that it has previously picked up certain rows of data then that is fine to do.


*This is what I had in my initial attempt:

Sub Apply_ChkSite_RAG()

LastCell = Last(4, Rng)

Dim i As Long
Dim i2 As Long
Dim r1 As Range
Dim r2 As Range

For i = 3 To 34
For i2 = 2 To LastCell

Set r1 = Range("SheetName!D" & i2)
Set r2 = Range("A" & i & ":BZ" & i)

If r1.Value = "G" Then r2 .Interior.Color = vbGreen
If r1.Value = "A" Then r2 .Interior.Color = vbOrange
If r1.Value = "R" Then r2 .Interior.Color = vbRed

Next i2

End Sub

Update. Have added an xlsx version of the live document with just a few cells completed with the current excel formulae on the third tab (this is Summary 2). The raw data table where users will enter info is on the first tab.