CODEWITHSUNDEEP
excelvbaexcel-4.0
Tatiana
Tatiana

Reputation: 21

Put value in cell based on font and backgroud color

I have a spreadsheet - on one of the tabs I have a table filled with names and they are related to a bunch of parameters which are color-coded as well as they have different background color.

I would need to create a formula to change those "X" based on colors to simple text (e.g. if a cell has green background and black "X", than I want to call it GB, if cell has yellow background and blue "X"- YB, etc.)

Update:

I have created two name ranges:

CellColor: =GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

FontColor: =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1))

I have figured our the numbers for fonts and background colors. But when I enter this formula it is not returning correct values:

=IF(AND(FontColor=3,I18="X"),"EXR",(IF(AND(FontColor=23,I18="X"),"BU",(IF(AND(Fo‌ntColor=0,I18="X"),"EPL",0)))))

If I place the formula in the column on the left of the cell, it works, if on another tab, it does not.

Upvotes: 2

Views: 2994

Answers (1)

user1274820
user1274820

Reputation: 8144

In VBA:

Sub SetValueBasedOnColors()
Dim c As Range
For Each c In Range("A2:A10")
    If c.Interior.Color = RGB(196, 215, 155) And c.Font.Color = RGB(0, 0, 0) Then
        c.Value = "GB"
    ElseIf c.Interior.Color = RGB(255, 255, 0) And c.Font.Color = RGB(31, 73, 125) Then
        c.Value = "YB"
    End If
Next c
End Sub

Results:

Results

You can get the interior color by right clicking on the cell and selecting:

Format Cells...->More Colors...->Custom (Tab)

Colors2

Colors3

If there are a lot of colors to work with, you can setup a "Color Table" to keep the code simple and to save you from having to look up each color.

Simply copy the cell with the colors and X into a range and enter what you would like them to be. Make sure you change the ColorTable range below to the correct range of your table.

Sub SetValueBasedOnColors()
Dim ColorTable As Range
Set ColorTable = Range("D2:D3") 'Point this range to your color table
Dim c As Range
Dim z As Range
For Each c In Range("A2:A10") 'Point this range to your data
    For Each z In ColorTable
        If c.Interior.Color = z.Interior.Color And c.Font.Color = z.Font.Color Then
            c.Value = z.Value
        End If
    Next z
Next c
End Sub

Colors4

Results:

Colors5

Upvotes: 2

Related Questions