Highlighting blank cells based on another value, but columns aren't always the same

[nfunkhouser]

I have a spreadsheet where I'm looking to highlight the Network ID if they put YES in the Network Requested field - the issue I'm having is that the people who submit these to us like to move things around, so I don't always know if the Network ID will be in the same column - sometimes it's in I, sometimes H, sometimes they move it to the end and it's in column BA. It's a pain, but sending them back isn't an option. Is there a way to do this using either VBA or conditional formatting, when I don't know what the column letters will be?

What it looks like:

What I'd like it to do - highlight the blank Network ID since they put YES in Network Requested but didn't provide a Network ID

 

[kvsrinivasamurthy]

Welcome.

 

[Peter_SSs]

If I'm understanding correctly, this would require us to alter the formula each time we get a spreadsheet back, is that right?

I wouldn't have thought so. Doesn't the table already exist when you give the workbook out?
If so you could have the CF applied before you send it if all the users might be doing is reordering/adding/deleting columns.

However, if you would prefer a macro to run when you get the workbook back, I think all you should need is this.
That is assuming ..

1. That is a formal Excel table as it appears to be in your original image.
2. That table is the only (or first indexed) table on the worksheet.
3. Columns "Network ID" and "Network Requested" columns appear somewhere in the table.

A possible advantage of this code is that if for some reason you edit values in "Network ID and/or "Network Requested" then any relevant yellow will appear/disappear without having to run the code again.

Sub Highlight_Yes_Blank()
  Dim lo As Object
  
  Set lo = ActiveSheet.ListObjects(1)
  With lo.ListColumns("Network ID").DataBodyRange
    .FormatConditions.Delete
    .FormatConditions.Add xlExpression, , "=AND(" & .Cells(1).Address(0, 0) & "=""""," & "INDIRECT(""" & lo.Name & "[@Network Requested]"")=""Yes"")"
    .FormatConditions(1).Interior.Color = vbYellow
  End With
End Sub