Multi Color Conditional Formatting based on cell value

[Jewnaa]

Dear Value Member
I need your guidance regarding this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Moon[/TD]
[TD]Sun[/TD]
[TD]Earth[/TD]
[TD]Ray[/TD]
[TD]Light[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]Moon[/TD]
[TD]Sun[/TD]
[TD]Moon[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]Ray[/TD]
[TD]Ray[/TD]
[TD]Light[/TD]
[TD]Old[/TD]
[TD]Moon[/TD]
[/TR]
[TR]
[TD]Light[/TD]
[TD]Light[/TD]
[TD]New[/TD]
[TD]Sun[/TD]
[TD]Ray[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Solution Required:

1. Same cell values' color based on other same values in different cells.
2. Identify unique value


Thanks

 

[dave3009]

You could quite easily test for uniqueness in the given range, you could also test for formatting if a value appears a certain number of times. If you wanted to determine if the value was x and was not unique you would need a list to test x against.

 

[lrobbo314]

How about this?

Sub test2()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim key, val

Set r = Range("A1:E4")
val = 3

For Each cel In r
    key = cel.Value
    If Not dict.exists(key) Then
        dict.Add key, val
        cel.Font.ColorIndex = val
        val = val + 1
    Else
        cel.Font.ColorIndex = dict.Item(key)
    End If
    
Next cel

End Sub

Hit Alt+F11 to open the VB editor.
Hit Alt + I + M to insert a module.
Paste the code.

You can adjust the

val = 3

line of code to change the color range of the fonts.

Also, the code assumes that your data is in range A1:E4. If it is not, you will need to adjust that portion of the code.

 

[Jeffrey Mahoney]

Jewnaa,

I've created tables that a triggered macro uses to define the fill and font colors. In your example, I would create a list with all the known items, giving them unique fill and font colors and then allow the macro to color those items that are not on the list a specific color like you have (Black, bolded). As you see new items, you can add them to your list with a new color definition.

Am I going in the right direction?

Jeff

 

[Jewnaa]

Working.
Thanks and much appropriated quick response.

 

[Jewnaa]

How about this?

Sub test2()
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim key, val

Set r = Range("A1:E4")
val = 3

For Each cel In r
    key = cel.Value
    If Not dict.exists(key) Then
        dict.Add key, val
        cel.Font.ColorIndex = val
        val = val + 1
    Else
        cel.Font.ColorIndex = dict.Item(key)
    End If
    
Next cel

End Sub

Hit Alt+F11 to open the VB editor.
Hit Alt + I + M to insert a module.
Paste the code.

You can adjust the

val = 3

line of code to change the color range of the fonts.

Also, the code assumes that your data is in range A1:E4. If it is not, you will need to adjust that portion of the code.

Working.
Thanks and much appropriated quick response.