Excel – Automatically filling colors as per the values in the cell in spreadsheet

microsoft excelspreadsheet

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 ?

Best Answer

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)

Related Solutions

Excel: conditionally format a cell using the format of another, content-matching cell

This uses cells in A1 to set the condition for cells in D1:D9 - alter ranges to suit your needs:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$A$1" and Sh.Name="Sheet1" Then
    Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color
    Sheets(1).Range("D1:D9").FormatConditions(1).Interior.Color = Target.Interior.Color
End If

End Sub

After changing the color, you will then have to change the cell by editing it, and pressing enter (so the change event is triggered)
the format conditions are ordered by the order the rules are applied. There are a lot more changes that could be applied, just add another .FormatConditions(1).Whatever = Target.Whatever to the code

This code does not set up any conditions, it will just alter the one(s) that are there. The conditions are numbered in the rule order displayed on screen

if you don't want to use conditional formatting, and just color the cells, then you can loop over each cell this way:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Or Sh.Name <> "Sheet1" Then Exit Sub
' exit if not in key range (formatting key is A:A on sheet1
If VarType(Target) > vbArray Then Exit Sub
' if multiple cells are changed at once, then exit, as i'm not going to fight with multi cell change

Dim TargetRange As Range
Dim lCell As Object

Set TargetRange = Sh.Range("D1:D9")
' changing cells in this area

For Each lCell In TargetRange.Cells
    If lCell.Value = Target.Value Then
    ' only change cells that match the edited cell
        lCell.Font.Color = Target.Font.Color
        lCell.Interior.Color = Target.Interior.Color
        ' copy whatever you feel needs to be copied
    End If
Next

End Sub

Excel – How to make a cell’s font transparent

I've found a work-around that doesn't change the font color, but effectively removes the text from the cells. Adjust the cell number format to Custom, with a value of ;;;.

Best Answer

Related Solutions

Excel: conditionally format a cell using the format of another, content-matching cell

Excel – How to make a cell’s font transparent

Related Question