Announcement

Collapse
No announcement yet.

Colour Cells Conditionally From Formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Colour Cells Conditionally From Formula

    I am trying to colour cells conditionally in my spreadsheet. I know you can do this with Conditional Formatting but I will need quite a few colours when I get right into it. The code below works if I just enter the value into cell AG5, but the value in the cell will be calculated via a formula. I imagine it is pretty easy to fix but I am pulling my hair out and would really appreciate a pointer in the right direction.

    Thanks
    Theo.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("AG5")) Is Nothing Then
            Select Case Target.Value
                Case 1 To 4
                    icolor = 43
                ' Case 6 To 10
                    ' icolor = 12
                Case Else
                    icolor = 5
            End Select
            
            Target.Offset(0, -13).Interior.ColorIndex = icolor
        End If
        
    End Sub

  • #2
    Re: Colour Cells Conditionally From Formula

    Hi,

    Put the code in between

    Private Sub Worksheet_Calculate()

    End Sub


    HTH
    Kris

    ExcelFox

    Comment


    • #3
      Re: Colour Cells Conditionally From Formula

      I did that and now I'm getting the following error:

      Code:
      Run-time error '424':
      
      Object required

      Comment


      • #4
        Re: Colour Cells Conditionally From Formula

        Hi,

        Try

        Dim Rng As Range, r As Range

        Set Rng = Range("T5:T25") 'adjust the range

        For Each r In Rng
        r.Interior.ColorIndex = Evaluate("LOOKUP(" & r.Offset(, 13).Address(0, 0) & _
        ",{0,6,13},{43,12,5})")
        Next
        Kris

        ExcelFox

        Comment


        • #5
          Re: Colour Cells Conditionally From Formula

          Kris,

          You hit the nail on the head!
          Thanks very much for your help.

          Theo.
          Auto Merged Post Until 24 Hrs Passes;

          I have another question regarding this...

          Would it be possible to include these kinds of calculations in this?

          Code:
          =AND($BG$2>=7,$BG$2<=13)
          Last edited by Theodjinn; August 8th, 2008, 20:07. Reason: Auto Merged Doublepost

          Comment

          Working...
          X