SOLVED

New Contributor

# Changing a cell value based on cell background colour

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

# Re: Changing a cell value based on cell background colour

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
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
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

# Re: Changing a cell value based on cell background colour

``````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.

Thank you