# Automatically filling colors as per the values in the cell in spreadsheet

Question : Is it possible to fill each cell in a spreadsheet with a color whose grayscale value is same as cell value?

Explanation :

Consider I have a spreadsheet as follows :

All values lie between 0-255 ( which corresponds to 256 grayscale colors). Now I want to fill will each cell with the color whose value is the value in the cell. So expected output is as follows :

Also, see that font color in each cell is either black or white corresponding to the background color.

Is there any simple method in Excel to perform this in a very few steps or all at once ?

-
ok, it is possible to do this with conditional-formats, but if there is the possibility, you should use a macro for it. – Jook Nov 8 '12 at 13:55

Create a Macro that iterates through your cells, reads the value in the cell and assigns it to the color like this:

``````Range("A1:A6").Interior.Color = RGB(200,160,35)
``````
-
+1 - It is quite simple – user172056 Nov 8 '12 at 17:10

TheUser1024 is right, and has my vote.

Here is a ready to use solution:

``````Public Sub Demo()
Dim cel As Range
Dim myRange As Range

Set myRange = Range("A1:E10")

For Each cel In myRange
cel.Interior.Color = RGB(cel.Value, cel.Value, cel.Value)
If cel.Value > 127 Then
cel.Font.Color = RGB(0, 0, 0)
Else
cel.Font.Color = RGB(255, 255, 255)
End If
Next cel
End Sub
``````

Edit:

This code won't break in case of strings or negative values ...

``````Public Sub Demo()
Dim cel As Range
Dim greytone As Long
Dim myRange As Range

Set myRange = Range("A1:E10")

For Each cel In myRange
greytone = CLng(Abs(Val(cel.Value)))
cel.Interior.Color = RGB(greytone, greytone, greytone)
If greytone > 127 Then
cel.Font.Color = RGB(0, 0, 0)
Else
cel.Font.Color = RGB(255, 255, 255)
End If
Next cel
End Sub
``````
-
This works if all cells contain integers from 1 to 256, but it will throw an error for anything else. You can add a condition in the for loop to check for this, and that should remove any error issues. – Excellll Nov 8 '12 at 15:42
@Excellll yes, you're right - but it works for empty cells and all positive double values. However, I edited it to a more robust version, thanks for the hint! – Jook Nov 8 '12 at 16:03
I believe this deserves more upvoting than my answer, since it does all the fine detail. Mine just states the basic principle and i did not even think of that font.color aspect. – TheUser1024 Nov 8 '12 at 17:31