Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Colour Cells Conditionally From Formula

  1. #1
    Join Date
    7th August 2008
    Posts
    5

    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.

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Colour Cells Conditionally From Formula

    Hi,

    Put the code in between

    VB:
    Private Sub Worksheet_Calculate() 
         
    End Sub 
    
    
    HTH

  3. #3
    Join Date
    7th August 2008
    Posts
    5

    Re: Colour Cells Conditionally From Formula

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Colour Cells Conditionally From Formula

    Hi,

    Try

    VB:
    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 
    
    

  5. #5
    Join Date
    7th August 2008
    Posts
    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?

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 8
    Last Post: August 8th, 2008, 16:50
  2. Conditionally Sum Cells From Multiple Sheets Macro
    By Dbock in forum Excel General
    Replies: 1
    Last Post: January 23rd, 2008, 12:48
  3. Conditionally Change Line Colour On Chart
    By davecurtis in forum TIP, TRICKS & CODE
    Replies: 6
    Last Post: December 21st, 2007, 23:34
  4. Conditionally Delete Cells
    By afguard17 in forum Excel General
    Replies: 2
    Last Post: December 14th, 2006, 14:47
  5. Highlight cells conditionally with VBA
    By londonal23 in forum Excel General
    Replies: 4
    Last Post: December 5th, 2004, 22:42

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno