SOLVED

Changing a cell value based on cell background colour

New Contributor

Hi for now I have created a separate formula CellColour for each cell 

 =GET.CELL(63,'22.04.27'!P3)  to   =GET.CELL(63,'22.04.27'!P17)

and set of nested if how to change a colour for corresponding cell =IF(CellColor=3,0.8*P3,IF(CellColor=44,0.9*P3,IF(OR(CellColor=43,CellColor=2),P3,IF(CellColor=33,1.1*P3,""))))

 Is there anything easier as I will have to do this to a few hundred sheets with different logical tests? I would love the VBA that I just have to change the logical test. I will be using the same background colours for every workbook.

3 Replies

@Bziku_maly 

How To Change Value Based On Cell Color In Excel?

Standard disclaimer: non Microsoft Site

Sub ChangeValueBasedOnCellColor()
    Dim rg As Range
    Dim xRg As Range
    Set xRg = Selection.Cells
    Application.DisplayAlerts = False
    For Each rg In xRg
        With rg
            Select Case .Interior.Color
                Case Is = 255 'Red
                    .Value = 1
                Case Is = 15773696 'Blue
                    .Value = 0
            End Select
        End With
    Next
    Application.DisplayAlerts = False
End Sub

Hope I could help you with these information / link.

 

NikolinoDE

I know I don't know anything (Socrates)

 

best response confirmed by Hans Vogelaar (MVP)
Solution

@Bziku_maly 

 

Sub color()

Dim i As Integer

For i = 3 To 50

Select Case Cells(i, 3).Interior.ColorIndex

Case Is = 44
Cells(i, 2).Value = 0.9 * Cells(i, 16).Value

Case Is = 43
Cells(i, 2).Value = 1 * Cells(i, 16).Value

Case Is = 33
Cells(i, 2).Value = 1.1 * Cells(i, 16).Value

Case Is = 2
Cells(i, 2).Value = 1 * Cells(i, 16).Value

Case Is = 3
Cells(i, 2).Value = 0.8 * Cells(i, 16).Value

End Select

Next i


End Sub

 

Maybe with these lines of code. Click the button in cell E2 in the attached file to start the macro.