VBA Highlight Cells When Other Value is Present

Question

I am looking to highlight certain cells in a spreadsheet when a value is entered in a different column. I realize this can be done with conditional formatting but, due to circumstances within the company, I must use VBA. (The spreadsheet is passed onto another program that can't read conditional formatting)

I am trying to highlight 11 different columns within my range of values whenever ANYTHING is entered into column L. For example, when a date it entered in L2, then C2, J2, K2, etc. are highlighted yellow.

Below is what I have come up with.. unfortunately, when I run the macro, nothing happens. I see it run, but I get no results - not even an error message. Thanks in advance for the time you take to help me out!

Dim rng As Range, r As Range

Set wb = ThisWorkbook
Set sht1 = wb.Sheets("From GIS")
Set sht2 = wb.Sheets("To MapCall")

Set rng = Intersect(sht2.UsedRange, Range("L:L")).Cells


For Each r In rng
    If r.Value = "" Then
        Cells(r.Row + 1, "C,J,K,Q,AI,AV,AW,AX,AY,AZ,BR").Interior.Color = RGB(255, 255, 0)

    End If
Next r

Answer 1

Please give this a try and change r.Value = "" to r.Value <> "" as you want to apply a color if something is entered in the range not when it is blank. Tweak it as per what you actually need. I am also not sure why have you used r.Row + 1? It that's not what you want, replace i = r.Row + 1 in the below code with i = r.Row.

Also it's a good practice to declare all the variables used in the code.

Dim wb As Workbook
Dim sht1 As Worksheet, sht2 As Worksheet
Dim rng As Range, r As Range, clrRng As Range
Dim i As Long
Set wb = ThisWorkbook
Set sht1 = wb.Sheets("From GIS")
Set sht2 = wb.Sheets("To MapCall")

Set rng = Intersect(sht2.UsedRange, Range("L:L")).Cells
For Each r In rng
    If r.Value = "" Then
        i = r.Row + 1
        Set clrRng = Union(Range("C" & i), Range("J" & i & ":K" & i), Range("Q" & i), Range("AI" & i), Range("AV" & i & ":AZ" & i), Range("BR" & i))
        clrRng.Interior.Color = RGB(255, 255, 0)
    End If
Next r

Answer 2

One method would be to create a union of ranges.

Sub test()

    Dim Rng As Range, r As Range, uRng As Range, row As Long

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("From GIS")
    Set sht2 = wb.Sheets("To MapCall")

    Set Rng = Intersect(sht2.UsedRange, Range("L:L")).Cells

    For Each r In Rng
        If r.Value <> "" Then
            row = r.row

            Set uRng = Union(Cells(row, "C"), Cells(row, "J"))  'Etc... Keep going with each column
            uRng.Interior.Color = RGB(255, 255, 0)

        End If
    Next r

End Sub