Select cell based on value and highlight to the last column with data

[thepeej]

I have data that includes merged cells (both vertically and horizontally) with various "Total" rows summarizing different levels of details. For a particular column, I need to find each row that says "Total" and highlight that cell and all cells to the end of that row, but I don't want to highlight to the last column in Excel (just to the end of my data) and I don't want to highlight to the left (because the column to the left is vertically merged).

Image attached. the blue, green, and pink lines are what I'm trying to achieve. (Tried to paste Mini-sheet but add-in not working on work computer).

Thanks!

 

[bebo021999]

The image did not show what last column is.
I assum its column I
Try 3 different conditioning formatting formulas with 3 different colours
Like this:
Stay in cell A1:
Level 1: "Total" in column B

=AND($B1="Total",COLUMN(A1)>=2,COLUMN(A1)<COLUMN($I:$I))

Level 2: "Total" in column C

=AND($C1="Total",COLUMN(A1)>=3,COLUMN(A1)<COLUMN($I:$I))

Level 3: "Total" in column E

=AND($E1="Total",COLUMN(A1)>=5,COLUMN(A1)<COLUMN($I:$I))

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7					Total
8			Total
9		Total
10
11					Total
12
13			Total
14
15
16
17
18									Last column
19									column I
Sheet6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:I15	Expression	=AND($B1="Total",COLUMN(A1)>=2,COLUMN(A1)<COLUMN($I:$I))	text	NO
A1:I15	Expression	=AND($C1="Total",COLUMN(A1)>=3,COLUMN(A1)<COLUMN($I:$I))	text	NO
A1:I15	Expression	=AND($E1="Total",COLUMN(A1)>=5,COLUMN(A1)<COLUMN($I:$I))	text	NO

 

[thepeej]

Thanks Bebo, that would work but i was hoping for a VBA solution. This is a report i receive weekly that i do a number of other formatting functions to via VBA...would like to add this to the existing code.

 

[bebo021999]

VBA solution

Option Explicit
Sub test()
Dim Lc&, Rng As Range, cell As Range
Lc = Cells(1, Columns.Count).End(xlToLeft).Column ' Last column
Set Rng = Range(Cells(1, 1), Cells(Cells(Rows.Count, "B").End(xlUp).Row, Lc))
    For Each cell In Rng
        If cell.Value Like "Total" Then
            With cell.Resize(, Lc - cell.Column)
                Select Case cell.Column
                    Case Is = 2
                        .Interior.ColorIndex = 41
                    Case Is = 3
                        .Interior.ColorIndex = 34
                    Case Is = 5
                        .Interior.ColorIndex = 40
                End Select
            End With
        End If
    Next
End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H	I
2
3
4
5
6
7					Total
8			Total
9		Total
10
11					Total
12
13			Total
14		Total
15
16
17
18									Last column
19									column I
20
21
22
Sheet1