Conditional Formatting by row NAME for multiple tabs

I have a spreadsheet with 150 tabs, I was asked to highlight all numbers under 30 red, in the row labeled TOTAL. The problem is, this row fluctuates between row 6 7 8 or 9 across all tabs... so I cant just simply do it for row 7 across all tabs, or it will highlight an unintended row. Any solution to this? Here's a small portion for reference.

bookey.xlsx

Please use this one, has < 1 Worksheet

bookey.xlsx
Here is the file, its just two tabs, but the original is 150, Just wanted to show you how it works. The TOTAL (in caps) row, is the row I need to have highlighted for values that occur below 30, that row fluctuates between rows 6-8 throughout all worksheets. "TOTAL" is also merged to include the percentages (but I can workaround that by using the condition "between" instead of < than).

Originally Posted by protonLeah

Since you didn't post a workbook, the best way to start:
1. start the macro recorder.
2. Search for "Total"
3. When found, select the entire row of data
4. Suppose "Total" is found in row 8 and you have 10 columns of data (column J). With cells a8 - J8 selected
Select Conditional Formatting and use "=A8<30" as the test formula.
5. Apply desired formatting.
Stop recording.
The macro can be used as the core to loop through the various tabs. You should post the workbook with the recorded macro and several worksheets with "Total" and some numbers in various rows.

---

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Rows("6:8").Select
Range("B6").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=1.0000001", Formula2:="=30"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub


I used this, but I'm basically just trying to do something like:
Rows("TOTAL",header=TRUE).Select, instead of Rows("6:8").Select