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

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.

     
     

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    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