Feb 17 2021 09:09 AM
Hi, I have the following table:
Each actual value is relating to the "Planned" value below. I would like each "Actual" value to have a red colour if the planned value is 0 or green if the number is equal to the "Planned" value below. Each field can have a different planned value, so I need a smart formula.
Are they any ways to do it? Thank you
Feb 17 2021 09:24 AM
Feb 17 2021 09:25 AM
Absolutely there are ways to do it.
If I were you, however, I'd put the cells side-by-side if you can. Makes it easier, and probably would make more sense design wise.
Anyway, here's an example. I used a random number generator just to put it through its paces. To get a whole new set of numbers, just activate the F9 key. With a mac that's done by holding the Fn key and pressing the key along the top row with F9 appearing on it.
Feb 17 2021 09:26 AM
Hi
I think this might be what you want? This uses conditional formatting and might help you?
BTW - is there any reason why you have to layout with actual above planned? If you have actual next to planned (as shown on the right hand side) that it is easier to understand and the formatting also is simpler.
hope this helps.
Peter
Feb 17 2021 09:29 AM
Feb 18 2021 01:41 AM
Well, the trick is - there will be thousands of rows like this, so setting this up manually would be a hell, also, changing format is impossible unfortunately
@PhilipTx @mathetes @peteryac60
Feb 18 2021 03:39 AM - edited Feb 18 2021 03:51 AM
Yes, my solution would probably only work in a limited environment and was a basic attempt to understand your issue. If you apply the rule itself to the first rows you just got just replicate the format. I can then only recommend to try a solution with visual basics. I used that in certain applications.
Example for visual basics:
Sub Kontrollkästchen61_Klicken()
If ActiveSheet.Shapes("Kontrollkästchen 61").OLEFormat.Object.value = 1 Then
Range("M67").Interior.ColorIndex = 6
Else
Range("M67").Interior.ColorIndex = xlNone
End If
ganzeZeile
End Sub
Sub Kontrollkästchen62_Klicken()
If ActiveSheet.Shapes("Kontrollkästchen 62").OLEFormat.Object.value = 1 Then
Range("N67").Interior.ColorIndex = 6
Else
Range("N67").Interior.ColorIndex = xlNone
End If
ganzeZeile
End Sub
Sub Kontrollkästchen63_Klicken()
If ActiveSheet.Shapes("Kontrollkästchen 63").OLEFormat.Object.value = 1 Then
Range("O67").Interior.ColorIndex = 6
Else
Range("O67").Interior.ColorIndex = xlNone
End If
ganzeZeile
End Sub
Sub ganzeZeile()
If ActiveSheet.Shapes("Kontrollkästchen 61").OLEFormat.Object.value = 1 And ActiveSheet.Shapes("Kontrollkästchen 62").OLEFormat.Object.value = 1 And ActiveSheet.Shapes("Kontrollkästchen 63").OLEFormat.Object.value = 1 Then
Range("A66:L68").Interior.ColorIndex = 6
Else
Range("A66:L68").Interior.ColorIndex = xlNone
End If
End Sub
There are likely more elegant or sophisticated approaches for this, but that would be a way to adress your issue. In future Excel will open up even further.