How to fill cell color based on more then 50 values (different color for each value)

[kishwarrathor]

Hi experts,
I will be thankful for my excel experts if they could help me to make a function mentioned in figure below,

Scene is that i have a color chart, And each color has a unique numeric id, What I want is that when I type a color id “for example 1 or 2 or 3” in a cell, it should be filed with its own color,

 

[Fishboy]

Hi experts,
I will be thankful for my excel experts if they could help me to make a function mentioned in figure below,

Scene is that i have a color chart, And each color has a unique numeric id, What I want is that when I type a color id “for example 1 or 2 or 3” in a cell, it should be filed with its own color,

Hi kishwarrathor,

Do they have to be specific colours based on your chart or are we able to pick a colour purely based on the number in the cell?

The following Worksheet Change macro will look at a cell as you update it and if it is not blank, it then checks to make sure the new value is a number. If the value is a number then it will change the fill of the cell to match the colour index of that number. If the cell value is blank it will remove the fill.

To test this out first make a COPY of your workbook. Now go to the sheet you want this to work on, right-click on the tab name and select View Code. In the new window that opens simply copy / paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' Define variable
Dim ColNo As Integer
    ' If target cell in not blank then...
    If Target.Value <> "" Then
        ' If the target value is a number then...
        If IsNumeric(Target.Value) Then
            ' Define variable ColNo as the number from the target cell
            ColNo = Target.Value
            ' Update the colour of the target cell to match the colour index of ColNo
            Target.Interior.ColorIndex = ColNo
        End If
    ' Else if the target cell is blank then...
    Else
        ' Remove the fill from the target cell
        Target.Interior.ColorIndex = xlNone
    End If
End Sub

 

[Fishboy]

Hmm, I've just noticed this will only work with values as high as 56 (the last ColorIndex value). If you have more than 56 possible values to check then it may be required to use RGB values to expand the possible colours available beyond the default 56.

 

[kishwarrathor]

Hi kishwarrathor,

Do they have to be specific colours based on your chart or are we able to pick a colour purely based on the number in the cell?

The following Worksheet Change macro will look at a cell as you update it and if it is not blank, it then checks to make sure the new value is a number. If the value is a number then it will change the fill of the cell to match the colour index of that number. If the cell value is blank it will remove the fill.

To test this out first make a COPY of your workbook. Now go to the sheet you want this to work on, right-click on the tab name and select View Code. In the new window that opens simply copy / paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
' Define variable
Dim ColNo As Integer
    ' If target cell in not blank then...
    If Target.Value <> "" Then
        ' If the target value is a number then...
        If IsNumeric(Target.Value) Then
            ' Define variable ColNo as the number from the target cell
            ColNo = Target.Value
            ' Update the colour of the target cell to match the colour index of ColNo
            Target.Interior.ColorIndex = ColNo
        End If
    ' Else if the target cell is blank then...
    Else
        ' Remove the fill from the target cell
        Target.Interior.ColorIndex = xlNone
    End If
End Sub

Thanks brother for taking interest in my post,
First thing that u asked about my color selection's reply is that i have to generate a custom color for my each value,
And second thing is that i can afford to be limited on 56 colors,
Thanks again,

 

[Fishboy]

Thanks brother for taking interest in my post,
I have to generate a custom color for my each value

So there isn't a set template of what colour each number should be, so long as each number has a unique colour?

And second thing is that i can afford to be limited on 56 colors

Just to clarify whether this was a typo or not, you CAN afford to be limited to 56 colours (meaning 56 is more than enough) or you CAN'T afford to be limited (meaning you may potentially need more than 56)?

 

[kishwarrathor]

So there isn't a set template of what colour each number should be, so long as each number has a unique colour?


Just to clarify whether this was a typo or not, you CAN afford to be limited to 56 colours (meaning 56 is more than enough) or you CAN'T afford to be limited (meaning you may potentially need more than 56)?

Well this is what i have, the presets of my some colors and there names,

And the other thing you discussed about typo or not, was not a typo,
But if there is a possibility of flexibility that will be highly admired,

 

[Fishboy]

Are the "Codes" in your example the same as some of the "values" we will be checking for, or are they purely for your own reference?

If they are not the cell values, can you give an example?

 

[kishwarrathor]

Are the "Codes" in your example the same as some of the "values" we will be checking for, or are they purely for your own reference?

If they are not the cell values, can you give an example?

These are purely for my own reference and will be typed manually only to represent a a color name and will not be used in any calculation,