How highlight range based on formula contents when select cell

[leap out]

Hi ,
in column g contains formulas . what I want when select cell in column G should highlight range based on formula contents.
for instance when select cell G2 . th formula contains =F2+F5+F8+F9+F10

RF.xlsm
	A	B	C	D	E	F	G
1	DATE	TYPE	BRAND	QTY	UNIT PRICE	TOTAL
2	01/01/2020	OIL	Q8 20W50 4x4L	200.00	20.00	4,000.00	82,185.00
3	02/01/2020	OIL	CAS 20W50 4x4L	220.00	24.00	5,280.00
4	03/01/2020	OIL	CAS 10W40 4x4L	120.00	29.00	3,480.00
5	04/01/2020	OIL	Q8 10W40 4x4L	140.00	22.00	3,080.00
6	04/01/2020	OIL	EN 10W40 4x4L	150.00	44.00	6,600.00	16,600.00
7	05/01/2020	OIL	EN 15W40 4x4L	200.00	50.00	10,000.00
8	05/01/2020	OIL	Q8 15W40 4x4L	111.00	55.00	6,105.00
9	06/01/2020	OIL	Q8 15W40 208L	200.00	250.00	50,000.00
10	07/01/2020	OIL	Q8 5W30 208L	100.00	190.00	19,000.00
11	07/01/2020	OIL	CAS 10W40 20L	200.00	60.00	12,000.00	20,760.00
12	08/01/2020	BATTERY	XX 70A L U	200.00	120.00	24,000.00	46,000.00
13	09/01/2020	BATTERY	XX 60A R V	200.00	110.00	22,000.00
14	10/01/2020	BATTERY	POWER 100A L FG	500.00	400.00	200,000.00	800,000.00
15	11/01/2020	BATTERY	POWER 100A L FG	600.00	450.00	270,000.00
16	12/01/2020	BATTERY	POWER 90A L FG	600.00	550.00	330,000.00
17	13/01/2020	BATTERY	DNG 90A L FG	600.00	530.00	318,000.00	318,000.00
OOL

Cell Formulas
Range	Formula
G2	G2	=F2+F5+F8+F9+F10
G6,G12	G6	=F6+F7
G11	G11	=F11+F4+F3
G14	G14	=F14+F15+F16
F2:F17	F2	=D2*E2
G17	G17	=F17

RF.xlsm
	G
2	82,185.00
OOL

Cell Formulas
Range	Formula
G2	G2	=F2+F5+F8+F9+F10

then should highlight range for each cell is existed in the formula like this

RF.xlsm
	A	B	C	D	E	F	G
1	DATE	TYPE	BRAND	QTY	UNIT PRICE	TOTAL
2	01/01/2020	OIL	Q8 20W50 4x4L	200.00	20.00	4,000.00	82,185.00
3	02/01/2020	OIL	CAS 20W50 4x4L	220.00	24.00	5,280.00
4	03/01/2020	OIL	CAS 10W40 4x4L	120.00	29.00	3,480.00
5	04/01/2020	OIL	Q8 10W40 4x4L	140.00	22.00	3,080.00
6	04/01/2020	OIL	EN 10W40 4x4L	150.00	44.00	6,600.00	16,600.00
7	05/01/2020	OIL	EN 15W40 4x4L	200.00	50.00	10,000.00
8	05/01/2020	OIL	Q8 15W40 4x4L	111.00	55.00	6,105.00
9	06/01/2020	OIL	Q8 15W40 208L	200.00	250.00	50,000.00
10	07/01/2020	OIL	Q8 5W30 208L	100.00	190.00	19,000.00
11	07/01/2020	OIL	CAS 10W40 20L	200.00	60.00	12,000.00	20,760.00
12	08/01/2020	BATTERY	XX 70A L U	200.00	120.00	24,000.00	46,000.00
13	09/01/2020	BATTERY	XX 60A R V	200.00	110.00	22,000.00
14	10/01/2020	BATTERY	POWER 100A L FG	500.00	400.00	200,000.00	800,000.00
15	11/01/2020	BATTERY	POWER 100A L FG	600.00	450.00	270,000.00
16	12/01/2020	BATTERY	POWER 90A L FG	600.00	550.00	330,000.00
17	13/01/2020	BATTERY	DNG 90A L FG	600.00	530.00	318,000.00	318,000.00
OOL

Cell Formulas
Range	Formula
G2	G2	=F2+F5+F8+F9+F10
G6,G12	G6	=F6+F7
G11	G11	=F11+F4+F3
G14	G14	=F14+F15+F16
F2:F17	F2	=D2*E2
G17	G17	=F17

and if I select another cell in column G delete color for previous cell has ever selected.

 

[rlv01]

Perhaps something like this.

' Place code in worksheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Me.Columns("G:G"), Me.UsedRange, Target) Is Nothing Then
        Dim rng As Range
        Dim FStr As String
        Dim S As Variant, SA As Variant

        With Me
            Set rng = .Range("A2:F" & .Range("A" & .Rows.Count).End(xlUp).Row)
            rng.Interior.ColorIndex = xlNone
        End With

        If Target.HasFormula Then
            FStr = Replace(Target.Formula, "=", "")
            FStr = Application.Trim(Replace(FStr, "+", " "))
            SA = Split(FStr)

            For Each S In SA
                Application.Intersect(rng, Me.Range(S).EntireRow).Interior.Color = vbYellow
            Next S
        End If
    End If
End Sub

Note that just as Col G formulas in your example only use the "+" operator, the above code works only for Col G formulas with just the "+" operator.

 

[Artik]

I don't know why to knock down an open door. Select the cell and click the mouse in the formula entry box. The cell will be in edit state. The cells that are the source of the formula will be highlighted.

Artik

 

[leap out]

Perhaps something like this.

wow !
this is really awesome !
can I implement your code by loop for all of sheets in workbook ,please?

 

[leap out]

Select the cell and click the mouse in the formula entry box. The cell will be in edit state. The cells that are the source of the formula will be highlighted.

I know and this doesn't solve my problem !
I would do that until check if I select right cells for th ID to calculation , then when highlight the ranges for the formula I can distinguish them and make easy to make sure to know if the ID is correct to calculating .

 

[rlv01]

wow !
this is really awesome !
can I implement your code by loop for all of sheets in workbook ,please?

Not by loop, because it's event code. But you could paste the same Private Sub Worksheet_SelectionChange(ByVal Target As Range) macro into the code module of each worksheet you want that behavior for.

Or you could convert the macro to workbook-level event code (not tested)

' Place code in workBOOK code module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Application.Intersect(Sh.Columns("G:G"), Sh.UsedRange, Target) Is Nothing Then
        Dim rng As Range
        Dim FStr As String
        Dim S As Variant, SA As Variant

        With Sh
            Set rng = .Range("A2:F" & .Range("A" & .Rows.Count).End(xlUp).Row)
            rng.Interior.ColorIndex = xlNone
        End With

        If Target.HasFormula Then
            FStr = Replace(Target.Formula, "=", "")
            FStr = Application.Trim(Replace(FStr, "+", " "))
            SA = Split(FStr)

            For Each S In SA
                Application.Intersect(rng, Sh.Range(S).EntireRow).Interior.Color = vbYellow
            Next S
        End If
    End If
End Sub

 

[leap out]

@rlv01
that's great !
thank you so much for your help.

 

[rlv01]