Match word in row, then conditional format other rows based on matched column value EXCEL

[Binho]

I have a spreadsheet where i want to search for the word "Total" in column headings, then look down the column that matched for "Total", find any row that has a value greater than 1 and highlight the row. The reason for searching for "Total" is that more columns may be added at any time but there will always be a "Total" column, and I will always want to highlight that row if the total is greater than 0.


I got very confused using the MATCH function . Any help would be appreciated.

 

[LloydFinancials]

Welcome Binho,

Here is what I suggest:

Assuming that the word "Total" appears once:

Sub test()Dim acol As Long
Dim i As Integer
For i = 2 To 9999
  acol = Application.Match("Total", Sheets("Sheet1").Range("A1:BZ1"), 0)
  If Cells(i, acol).Value > 0 Then
    Cells(i, acol).Interior.ColorIndex = 8
  End If
Next i
End Sub