Conditional format match highlight in red font

[motilulla]

Using Excel 2010
Hello,

I need Conditional format VBA or formula highlight matches in red font.

For example I have some join pattern separated by vertical bar in range C6:C14 and results in range E1:H1 in the separate cells

I want highlight pattern value in red as result match with E1:H1 as per example show below

CF.xlsm
	A	B	C	D	E	F	G	H	I
1					1	1	2	1
2
3
4
5
6			2 | 1 | 1 | 1
7			2 | 1 | 1 | X
8			2 | 1 | 1 | 2
9			2 | 1 | X | 1
10			2 | 1 | X | X
11			2 | 1 | X | 2
12			2 | 1 | 2 | 1
13			2 | 1 | 2 | X
14			2 | 1 | 2 | 2
15
16
17
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:C14	Expression	=SI(C6=AI$1&" | "&AJ$1&" | "&AK$1&" | "&AL$1;1;0)	text	NO

Regards,
Moti

 

[kevin9999]

Hi Moti,
This might do until something more elegant comes along:

Option Explicit
Sub MotRed()
    Application.ScreenUpdating = False
    Dim r As Range, c As Range
    Set r = Cells(6, 3).CurrentRegion
    r.Font.Color = vbBlack
    Dim i As Long, a, b
    a = Application.Transpose(Cells(1, 5).Resize(1, 4))
    b = Array(1, 5, 9, 13)
    For Each c In r
        For i = LBound(b) To UBound(b)
                If Mid(c, b(i), 1) = CStr(a(i + 1, 1)) Then c.Characters(b(i), 1).Font.Color = vbRed
        Next i
    Next c
    Application.ScreenUpdating = True
End Sub

 

[motilulla]

Hi Moti,
This might do until something more elegant comes along:

Option Explicit
Sub MotRed()
    Application.ScreenUpdating = False
    Dim r As Range, c As Range
    Set r = Cells(6, 3).CurrentRegion
    r.Font.Color = vbBlack
    Dim i As Long, a, b
    a = Application.Transpose(Cells(1, 5).Resize(1, 4))
    b = Array(1, 5, 9, 13)
    For Each c In r
        For i = LBound(b) To UBound(b)
                If Mid(c, b(i), 1) = CStr(a(i + 1, 1)) Then c.Characters(b(i), 1).Font.Color = vbRed
        Next i
    Next c
    Application.ScreenUpdating = True
End Sub

Hello kevin9999, for me if solution works it is well-designed. I am happy it worked excellent for me.

Hope you have a wonderful week ahead! Good Luck.

Kind regards,
Moti

 

[kevin9999]

Happy to help Moti, and thanks for the feedback