Want to highlight cell based on intersection row and column vba excel

[elkaa]

Hi members.

I'm looking for a formula / vba code of finding the intersection between a column and a line.
I have 2 values from another sheet that i need to match them with row and column head on another sheet and intersection need to be highlighted.
would be great if someone can help me with it.
thankyou so much

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	12	hi				hi	ho	lo	ko	io	po
2	45	ho			45
3	25	lo			45
4	54	ko			45
5	12	io			22
6	22	po			12
7					25
8					16
9					16
10					16
11					45
12					45
13					45
Sheet24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:K13	Expression	=IF(ISERROR(MATCH(F$1&"-"&$E2,$B$1:$B$6&"-"&$A$1:$A$6,0)),FALSE,TRUE)	text	NO

 

[elkaa]

thank you for the help i don't want to use conditional formatting if i can have vba code or formula would be grate thank you

 

[JamesCanale]

I find VBA tedious. How would you like it highlighted in a formula?

 

[elkaa]

Expression

=IF(ISERR

i am look too so i don't have much experience in it maybe combination of vertical and horizontal loop thank you so much for the help

 

[JamesCanale]

I can't say that I understand what you want, but, this bit will put some text into the cells with a match.

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	12	hi				hi	ho	lo	ko	io	po
2	45	ho			45		XX
3	25	lo			45		XX
4	54	ko			45		XX
5	12	io			22						XX
6	22	po			12	XX				XX
7					25			XX
8					16
9					16
10					16
11					45		XX
12					45		XX
13					45		XX
Sheet24

Cell Formulas
Range	Formula
F2:K13	F2	=IF(ISERROR(MATCH(F$1&"-"&$E2,$B$1:$B$6&"-"&$A$1:$A$6,0)),"","XX")

 

[elkaa]

Public Function GetTableVal(ByVal tblName As String, ByVal rowName As String, ByVal colName As String) As Boolean

On Error Resume Next
Dim rng As Range
Set rng = Range(tblName)
On Error GoTo 0

If rng Is Nothing Then
GetTableVal = "ERROR: Table not found"
Exit Function
End If

Dim tbl As ListObject
Set tbl = rng.ListObject

On Error Resume Next
Dim colIndex As Long
colIndex = tbl.ListColumns(colName).Index
On Error GoTo 0

If colIndex = 0 Then
GetTableVal = "ERROR: Column not found"
Exit Function
End If

Dim rowIndexRange As Range
Set rowIndexRange = tbl.ListColumns(4).Range.Find(rowName, LookIn:=xlValues, LookAt:=xlWhole)

If rowIndexRange Is Nothing Then
GetTableVal = "ERROR: Row not found"
Exit Function
End If

Dim rowIndex As Long
rowIndex = rowIndexRange.Row - tbl.Range.Row + 1

Dim res As Range
Set res = tbl.Range(rowIndex, colIndex)
res.Interior.ColorIndex = 55

GetTableVal = True

End Function

found this code but it's to get value from intersection between row and colonne i wanted to change it to colore the cell but it's didn't work when i try to use interor.colorIndex on res nothing is done.if somone can tel me why i will be thankful

 

[GaryG9595]

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	12	hi				hi	ho	lo	ko	io	po
2	45	ho			45
3	25	lo			45
4	54	ko			45
5	12	io			22
6	22	po			12
7					25
8					16
9					16
10					16
11					45
12					45
13					45
Sheet24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:K13	Expression	=IF(ISERROR(MATCH(F$1&"-"&$E2,$B$1:$B$6&"-"&$A$1:$A$6,0)),FALSE,TRUE)	text	NO

Hello, can this be modified to only search B1 then A1, not $B$1:$B$6&"-"&$A$1:$A$6. I have tried $B$1&"-"&$A$1, and that didn't work?

 

[kevin9999]

VBA option, please try it on a copy of your workbook (change the sheet names to suit).

Option Explicit
Sub elkaa()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, a
    Set ws1 = Worksheets("Sheet1")          '<-- *** Change the sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    With ws1.Range("A1", ws1.Cells(Rows.Count, "A").End(xlUp))
        a = Evaluate(.Address(, , , 1) & "&""|""&" & .Offset(, 1).Address(, , , 1))
    End With
    
    Dim LRow As Long, LCol As Long, i As Long, j As Long, s As String
    LRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    LCol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
    ws2.Range(ws2.Cells(2, 2), ws2.Cells(LRow, LCol)).ClearFormats
    
    With ws2
        For i = 2 To LRow
            For j = 2 To LCol
                s = .Cells(i, 1) & "|" & .Cells(1, j)
                If Not IsError(Application.Match(s, a, 0)) Then
                    .Cells(i, j).Interior.Color = vbRed
                End If
            Next j
        Next i
    End With
    Application.ScreenUpdating = False
End Sub