Conditional Formatting

[HoMachine]

Hi all,

I have a chart Column A with bunch of names, and i want to highlight all the duplicated names, in different colors.
Below example, I want to highlight Ben maybe in red and John in Green and Joe in Blue (random colors)

Is it possible? using Excel 2010

Column A
Ben
Ben
John
Carlie
Troy
Joe
Greg
Bill
John
Joe

Thanks,

 

[Jambi46n2]

There are a number of different ways to achieve this..

Here's a few links to see which would work best for you:

https://exceljet.net/lessons/how-to-highlight-text-values-with-conditional-formatting

https://exceljet.net/formula/highlight-cells-that-equal

 

[HoMachine]

Thanks Jambi
Because the "names" changes randomly and i cannot specify exactly what will be duplicates and what will not be, i've built a chart that shows the dups and referenced using that table.

So, using what you've suggested, i've built the following:

the only thing i'm having an issue with now is the .tintandshade value. can someone help me with the value?

Basically i want to random Light color Cell/Dark Color Font of the same color, eg. Light Red Cell/Dark Red Color, & Light Blue Cell and Dark Blue Color


Dim DLROW As Integer
Dim FNEROW As Integer
Dim X As Integer




X = 2




DLROW = Range("L" & Rows.Count).End(xlUp).Row
FNEROW = Range("J1").End(xlDown).Row


Do While Worksheets("CDS - Option Dup List").Range("C" & X).Value <> ""


Range("L" & FNEROW & ":L" & DLROW).Select

Selection.FormatConditions.Add Type:=xlTextString, String:= _
"='name'!$C$" & X, TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.3
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.31
End With
Selection.FormatConditions(1).StopIfTrue = False

X = X + 1
Loop

 

[Jambi46n2]

I'm glad you found my previous post useful.

Personally I've never used excel / vba to generate random values or colors.

After some research I found a thread suggesting something similar that might work if you modify it to suit your needs:

https://www.ozgrid.com/forum/forum/...vba-color-generator-and-color-reference-chart

Public pubPrevColor As Integer    ' Set as public so value is remembered

Function intRndColor()
   'USE - FUNCTION TO PICK RANDOM COLOR, ALSO ALLOWS EXCLUSION OF COLORS YOU DON'T LIKE
    Dim Again As Label
Again:
    intRndColor = Int((50 * Rnd) + 1) 'GENERATE RANDOM IN

    Select Case intRndColor
    Case Is = 1, 3, 21, 35, 36    'COLORS YOU DON'T WANT
        GoTo Again
    Case Is = pubPrevColor
        GoTo Again
    End Select
    pubPrevColor = intRndColor    'ASSIGN CURRENT COLOR TO PREV COLOR
End Function

Sub ViewColors()
'USE THIS TO VIEW COLORS, PICK COLORS YOU DON'T WANT FOR RANDOM COLOR GENERATOR
    Dim x As Integer
    Sheets.Add
    Cells(1, 1).Value = "Color Index#"
    Cells(1, 2).Value = "Color Sample"

    For x = 2 To 58
        Cells(x, 1).Value = x - 2
        Cells(x, 2).Select
        With Selection.Interior
            .ColorIndex = x - 2
            .Pattern = xlSolid
        End With
    Next x
    
    Cells.Select
    Cells.EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Cells(1, 1).Select 'GO HOME [COLOR=#252C2F][FONT=Courier]End Sub
[/FONT][/COLOR]

Note, Random color generator can be set to 56....

 

[HoMachine]

Thanks man, helped alot

 

[Jambi46n2]