Mirror Conditional formatting

[Lazaruss]

Hello

I have 2 sheets, Sheet1 has a list of recipie ingredients in each column Sheet2 has the below formula in each cell to calculate how many times a specific ingredient is used

=COUNTIF(Sheet1!$A$1:Sheet1!$Z$16000,Sheet1!A1)

I have setup a graded color scale conditional formatting on sheet2 so that the colour of the cell is darker as the number gets higher. So the more an ingredient is used the darker the cell.

What I would like to do is to mirror the cell colors applied by the graded color scale conditional formatting on sheet2 to the cells with the ingredient names in Sheet2.

Now I understand this is difficult because the CF background colour does not us Interior.Color so a work around would be to create VBA code which mimics CF but uses Interior.Color to set the background colour. After alot of googling I found code which does this and modified it so it mimics the CF I had setup on Sheet2:

Sub ColouringIn()

    Dim intColIndex As Integer
    Dim dblMax As Double
    Dim dblMin As Double
    Dim rngCell As Range

    dblMax = Application.WorksheetFunction.Max(Sheet2.Range("A1:ZZ100"))
    dblMin = Application.WorksheetFunction.Min(Sheet2.Range("A1:ZZ100"))

    For Each rngCell In Sheet2.Range("A1:ZZ100")
        If IsNumeric(rngCell.Value) And rngCell.Value <> "" Then
            intColIndex = (rngCell.Value - dblMax) / (dblMin - dblMax) * 255
            rngCell.Interior.Color(Sheet1.Range("A1:ZZ100")) = RGB(255, intColIndex, intColIndex)
        End If
    Next rngCell
   

End Sub

I have little coding experience so all I was able to do was apply the background colour to the cells in sheet2 but couldnt work out how to get the corresponding cells in Sheet1 to do the same. I understand its probably a small change to the above code but it is beond my coding capabilitys.

Thanks!