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

bat18

Board Regular
Joined
Mar 29, 2010
Messages
89
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
try these codes
Code:
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

Board Regular
Joined
Mar 29, 2010
Messages
89
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,568
Messages
5,572,974
Members
412,491
Latest member
tweetytoon
Top