Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 :

enter image description here

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 :

enter image description here

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 ?

share|improve this question
    
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
    
What have you tried? –  Excellll Nov 8 '12 at 15:12

2 Answers 2

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
share|improve this answer
    
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

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)
share|improve this answer
    
+1 - It is quite simple –  user172056 Nov 8 '12 at 17:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.