Conditional Formatting help

[sachin30us]

Hey guys..


I need some help

So sheet 1 has the below data

Cell A2, A3 and A4 have 1, 2, 3 respectively and

Cell B1, C1 and D1 have 2, 3, 4 respectively

I want excel to highlight matching cells in B1, C1 and D1 by searching A1,A2 and A3

Is there a formula or macro that can do that?

 

[Alan_P]

Hi sachin30us,

This code 'should' match the numbers, then give the matching pairs a random colour...

Sub MatchGiveRandomColour()
RandNum = Evaluate("RandBetween(0,56)")
LastRw = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To LastRw
    For x = 2 To LastCol
        If Range("A" & i).Value = Cells(1, x).Value Then
        
            Range("A" & i).Interior.ColorIndex = RandNum
            Cells(1, x).Interior.ColorIndex = RandNum
        
            RandNum = Evaluate("RandBetween(0,56)")
        
        End If
    Next
Next
End Sub

May not be exactly what your after but is a start...

Hope this helps,
Cheers,
Alan.