Пример макроса: Закраска в зависимости от содержимого

Sub TestMultipleFinds()  '' e22
    Dim MyRange As Range, OldRange As Range, FindStr As String
    Dim BlueColor As Integer, GreenColor As Integer
    
    'Look for first instance of "Light & Heat"
    Set MyRange = Sheets("LH").UsedRange.Find("Light & Heat")
    
    'If not found then exit
    If MyRange Is Nothing Then Exit Sub
    
    'Display first address found
    MyRange.Interior.color = RGB(255, 0, 0)
    
    'Make a copy of the range object
    Set OldRange = MyRange
    
    'Add the address to the string delimiting with a "|" character
    FindStr = FindStr & "|" & MyRange.Address
    
    'Iterate through the range looking for other instances
    GreenColor = 100
    BlueColor = 0
    Do
        'Search for "Light & Heat" using the previous found address as the After parameter
        Set MyRange = Sheets("LH").UsedRange.Find("Light & Heat", After:=Range(OldRange.Address))
    
        'If the address has already been found then exit the do loop – this stops continuous looping
        If InStr(FindStr, MyRange.Address) Then Exit Do
        
        'Display latest found address
        MyRange.Interior.color = RGB(0, GreenColor, BlueColor)
        
        ' Change cell's colors
        GreenColor = GreenColor - 100
        If GreenColor < 1 Then GreenColor = 100
        BlueColor = BlueColor + 100
        If BlueColor > 255 Then BlueColor = 0
        
        'Add the latest address to the string of addresses
        FindStr = FindStr & "|" & MyRange.Address
    
        'make a copy of the current range
         Set OldRange = MyRange
    Loop
End Sub

(function() {
    var oSheet = Api.GetSheet("LH");
    var oRange = oSheet.GetUsedRange();

    var MyRange = oRange.Find("Light & Heat", "", "xlValues", "xlWhole", "xlByColumns", "xlNext", true);
    if (MyRange == null) {
        return;
    }
    MyRange.SetFillColor(Api.CreateColorFromRGB(255, 0, 0));
    
    var OldRange = MyRange;
    var FindStr = "|" + MyRange.Address;
    
    var GreenColor = 100;
    var BlueColor = 0;

    do {
        MyRange = oSheet.Cells.Find("Light & Heat", OldRange, "xlValues", "xlWhole", "xlByColumns", "xlNext", true);
        if (FindStr.includes(MyRange.Address)) {
            break;
        }
        
        MyRange.SetFillColor(Api.CreateColorFromRGB(255, GreenColor, BlueColor));
        GreenColor -= 100;
        if (GreenColor < 1) {
            GreenColor = 100;
        }
        BlueColor = BlueColor + 100
        if (BlueColor > 255) {
            BlueColor = 0;
        }
        FindStr = FindStr + "|" + MyRange.Address;
        OldRange = MyRange;
    } 
    while(1);
})();