Forum Discussion

Bziku_maly's avatar
Bziku_maly
Copper Contributor
Apr 28, 2022
Solved

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 ...
excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Apr 28, 2022

    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.

     
     
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Apr 28, 2022

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)

 

Resources