Apr 28 2022 02:27 AM
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.
Apr 28 2022 02:44 AM
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.
I know I don't know anything (Socrates)
Apr 28 2022 02:50 AM
Solution
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.
Apr 28 2022 02:50 AM
Solution
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.