click and highlight

[Prasanna arachchi]

Hi There

I have two table call costing and cost summary as shown below

I need a formular or link , When i click any combination in costing table it should highlight cells in cost summary table.

for example when i click "E7" can it automatically highlighted " O13 ( or K13:O13)

Thank you

	ACA MACS Costing
Number of Beams	Clear Opening Width	Beam Clear Opening		Cost Summary
1500	1750	2000	2250	2500	2750	3000		MACS	Total cost
1	1250	6	6	8	8	10	10	12		CEMACS125150D6	$ 3,681
1	1500	6	6	8	8	10	10	12		CEMACS150150E6	$ 4,263
1	1750	6	6	8	8	10	10	12		CEMACS200150D6	$ 4,824
1	2000	6	6	8	8	10	10	12		CEMACS175175D6	$ 4,821
1	2250	6	6	8	8	10	10	12		CEMACS150250D10	$ 5,397
2	2500	9	9	12	12	15	15	18		CEMACS200200D8	$ 5,885
2	2750	9	9	12	12	15	15	18		CEMACS250250D15	$ 8,621
2	3000	9	9	12	12	15	15	18		CEMACS350200D12	$ 9,530
2	3250	9	9	12	12	15	15	18		CEMACS325250D15	$ 10,152
2	3500	9	9	12	12	15	15	18		CEMACS350250D15	$ 10,791
3	3750	12	12	16	16	20	20	24		CEMACS300300D18	$ 10,902
3	4000, To infinity in 250mm increments	12	12	16	16	20	20	24		CEMACS400300D18	$ 14,451
									CEMACS275275D15
Please click - Geen cells IN ABOVE TABLE to see the detail costing (costing has been completed only for these items )		MACS400275D15	$ 12,400.87

 

[bebo021999]

1- I think VBA can help. Is VBA acceptable?
2- When you click E7 (2000 and 2000) why you expect cells K13:O13 (350250) highlighted? (It should be 200200 in row 19, right?)

 

[Prasanna arachchi]

1- I think VBA can help. Is VBA acceptable?
2- When you click E7 (2000 and 2000) why you expect cells K13:O13 (350250) highlighted? (It should be 200200 in row 19, right?)

yes " it should be 200x200 row 9 which is correct !
VBA is acceptable as if it is simple me to handle , i am not very competent with VBA but i can manage it
thank you very much for your message

 

[Prasanna arachchi]

1- I think VBA can help. Is VBA acceptable?
2- When you click E7 (2000 and 2000) why you expect cells K13:O13 (350250) highlighted? (It should be 200200 in row 19, right?)

Hi bebo
can you please provide me with aba code
Thanks you

 

[bebo021999]

This code is an event handler that runs automatically whenever a cell selection changes in the Excel worksheet. It highlights related data when you click on specific cells.

Book1.xlsm

drive.google.com

Option Explicit
' This subroutine runs automatically when the selection changes in the worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long, st As String, fcell As Range

    ' Find the last used row in column K (by starting at bottom and moving up)
    lr = Cells(Rows.Count, "K").End(xlUp).Row
    
    ' Exit the subroutine if:
    ' 1. The selected cell(s) are NOT within range C4 to I[last row], OR
    ' 2. More than one cell is selected
    If Intersect(Target, Range("C4:I" & lr)) Is Nothing Or Target.Count > 1 Then Exit Sub
    
    ' Clear any existing color formatting in columns K and L (from row 4 to last row)
    Range("K4:L" & lr).Interior.Color = xlNone
    
    ' Create a search string by combining:
    ' - First 3 characters from column B in the selected row
    ' - First 3 characters from row 3 in the selected column
'for ex: st = "200350"
    st = Left(Cells(Target.Row, "B"), 3) & Left(Cells(3, Target.Column), 3)
    
    ' Search for the string in column K (from row 4 to last row)
    ' The "*" are wildcards meaning "any characters before/after our search string"
    Set fcell = Range("K4:K" & lr).Find("*" & st & "*")
    
    ' If a matching cell is found, color it and the cell next to it (in column L) yellow
    If Not fcell Is Nothing Then fcell.Resize(1, 2).Interior.Color = vbYellow
    
End Sub