HIGHLIGHTS SETS OF 4 EQUAL VALUES WITH DIFFERENT COLORS

[RIC63]

I need to highlight each set of four equal values found in the 4 columns, each set with a different color.
Values that repeat two or three times should not be highlighted.
Eg. 60 is present in all 4 columns so it must be highlighted eg. in red, 67 in yellow and so on

thanks if anyone can help me about it

 

[Eric W]

Could a number show up in the same column more than once? If so, how do you want to handle that? How many rows does your data have? Do you have any idea how many sets of 4 you might have? Conditional Formatting probably won't work for you, since each custom formula only allows 1 color. (Some built-in CF rules have multiple colors, but I don't see how to use those here.) If you only expect a limited number of sets, we could set up a limited number of rules (3 or 4?) that might work. Otherwise, you'll need a VBA macro to do this. Would that work for you? Even a macro though would require a list of the color values you want to highlight the cells with, so if you have a lot of sets, we'll probably end up repeating some colors. Let me know.

 

[RIC63]

Could a number show up in the same column more than once? If so, how do you want to handle that? How many rows does your data have? Do you have any idea how many sets of 4 you might have? Conditional Formatting probably won't work for you, since each custom formula only allows 1 color. (Some built-in CF rules have multiple colors, but I don't see how to use those here.) If you only expect a limited number of sets, we could set up a limited number of rules (3 or 4?) that might work. Otherwise, you'll need a VBA macro to do this. Would that work for you? Even a macro though would require a list of the color values you want to highlight the cells with, so if you have a lot of sets, we'll probably end up repeating some colors. Let me know.

Hi Eric,
first of all thank you for your support

below are the answers to your questions

Could a number show up in the same column more than once? NO How many rows does your data have? Do you have any idea how many sets of 4 you might have? FROM 2 TO 10 I THINK

Conditional Formatting probably won't work for you, since each custom formula only allows 1 color. (Some built-in CF rules have multiple colors, but I don't see how to use those here.) If you only expect a limited number of sets, we could set up a limited number of rules (3 or 4?) That might work. Otherwise, you'll need a VBA macro to do this. Would that work for you?

Yes I have tried several solutions but I agree with you that I need a macro in VBA, I have found one that does a similar thing but I cannot modify it for my purpose

Thanks again

 

[Eric W]

OK, give this a shot:

Open a COPY of you workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Copy the following code into the window that opens:

Sub Highlight4()
Dim ul As Range, d As Variant, colors As Variant, ctr As Long
Dim a1 As Long, a2 As Long, a3 As Long, a4 As Long

    Set ul = Range("A1")
    d = ul.Resize(ul.End(xlDown).Row - ul.Row + 1, 4).Value
   
    colors = Array(vbRed, vbYellow, RGB(20, 200, 40), 15773696)
    ctr = 0
    On Error GoTo NoMatch:
       
    For a1 = 1 To UBound(d)
        a2 = WorksheetFunction.Match(d(a1, 1), WorksheetFunction.Index(d, 0, 2), 0)
        a3 = WorksheetFunction.Match(d(a1, 1), WorksheetFunction.Index(d, 0, 3), 0)
        a4 = WorksheetFunction.Match(d(a1, 1), WorksheetFunction.Index(d, 0, 4), 0)
        ul.Offset(a1 - 1, 0).Interior.Color = colors(ctr)
        ul.Offset(a2 - 1, 1).Interior.Color = colors(ctr)
        ul.Offset(a3 - 1, 2).Interior.Color = colors(ctr)
        ul.Offset(a4 - 1, 3).Interior.Color = colors(ctr)
        ctr = (ctr + 1) Mod (UBound(colors) + 1)
NextA1:
    Next a1
   
    Exit Sub
   
NoMatch:
    Resume NextA1:
   
End Sub

In the first executable line, set the upper left corner of your range (A1 in this example). Two lines below that, put the colors you want to highlight with. I showed 3 ways to identify them. There are 8 colors you can identify with a vb constant: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, and vbWhite. Or you can use the RGB function to define a custom color, or just use the full number which you can find by looking at the range.interior.color property.

Now go back to Excel, press Alt-F8 to open the macro selector, choose Highlight4 and click Run. On your example, I got this result:

Book1
	A	B	C	D
1	60	19	76	61
2	11	61	64	60
3	62	20	61	10
4	40	52	10	67
5	77	10	69	19
6	67	63	18	20
7	9	70	48	70
8	82	60	50	30
9	16	55	53	76
10	80	30	67	48
11	22	48	60	52
12	29	25	37	63
13	33	49	4	16
14	86	56	30	82
15	14	86	36	55
16	66	67	55	9
17	90	5	12	62
18	43	15	16	12
19	10	31	82	22
20	57	33	35	64
21	59	54	73	43
22	2	36	70	90
23	30	72	63	73
24	70	23	17	86
25	8	57	27	40
26	15	79	78	24
27	47	87	79	33
28	19	6	14	50
29	49	24	24	14
30	71	12	43	77
Sheet4

Note that 60 and 70 are both highlighted in red. This is because there are 5 sets, but I only defined 4 colors in the array, so the macro started reusing them. If you put additional colors in the array, this might not happen.

Good luck!

 

[RIC63]

OK, give this a shot:
.....

Morning Eric

it's all very clear that's what I was trying to achieve, thank you

Ric