Forum Discussion

Wojciech_Plonka's avatar
Wojciech_Plonka
Copper Contributor
Feb 17, 2021

Conditional formatting based on a field below

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 n...
excel
Formulas and Functions
PhilipTx's avatar
PhilipTx
Copper Contributor
Feb 17, 2021

Wojciech_Plonka 

I tried to replicate what you ask for. Something like that?

  • Wojciech_Plonka's avatar
    Wojciech_Plonka
    Copper Contributor
    Feb 18, 2021

    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 

    • PhilipTx's avatar
      PhilipTx
      Copper Contributor
      Feb 18, 2021

      Wojciech_Plonka 

      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.