Using more than 3 conditional formats with match function for each one

[bat18]

I have 5 columns of different values on a spreadsheet, each day i paste in a set of values into a different column. These values will match to one value in any one of the 5 columns, if it does I need part of the row that value is on to be highlighted.

But depending on which column has been matched to I need to row to be highlighted a different colour. I used to use conditional formatting as i used to only have 3 columns to match to and it worked fine but now i'm stuck. I understand I have to write some sort of VBA code but was hoping for some advice as to how to do it.

Thanks

 

[ravishankar]

Hi
try these codes

Sub Bat18()
Dim a As Long, b As Long, x As Long, y As Long
x = Cells(Rows.Count, 6).End(xlUp).Row
On Error Resume Next
    For a = 1 To x
        For b = 1 To 5
        y = Cells(Rows.Count, b).End(xlUp).Row
        Cells(1, 26) = "=match(F" & a & "," & Chr(b + 64) & 1 & ":" & Chr(b + 64) & y & ",0)"
            If Cells(1, 26) > 0 Then
            Cells(Cells(1, 26), b).Interior.ColorIndex = 6
            End If
        Next b
    Next a
MsgBox "complete"
End Sub

it highlights the matching cell yellow. it does not highlight if there are more than one occurance of that word
Ravi

 

[bat18]

Cheers for the quick reply,

What i'm trying to get is depending on which column the value matches to part of its row will change colour. For example

Values in G, H, I, J, K

I enter a value in column U and it matches to cell K100

I now want cells A100:E100 highlighted red, but if it matched to a cell in column G, H, I or J i would want those cells highlited in a different colour.

Does this make any sense? I am just starting to learn VBA as well so need any help you can give, cheers