Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Conditional Formatting

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to colour individual cells in a large section of a worksheet,20 columns by 600 rows so that there are up to 10 different colours dependent on the value in each cell.
    As I work on the sheet the values in the cells change and I need the colours to change as I go. Conditional formatting works just fine but there are not enough values available (3 choices only). Is it possible to add more choices. I am very limited in VB ability.
    Yours hopefully Owen Kenny

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes it is possible, a few questions:

    (1) What is your range of cells?
    (2) What is your range of values for each color? Example, 0 - 50 would be yellow, 51 - 100 would be green, etc.
    (3) Are the values being added manually or are they calculated as the result of formulas in those cells?

    Answer these questions and we can assist more efficiently.


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    also, try doing a search on this board for conditional formatting, i know its been answered before

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Currently I am using this macro but it does not update until I run the macro and I am using it repearedly and the time wasted is terrible.
    This contains the ranges and the colours I am currently using.
    The values change as a result of formulas in the cells.
    Thank you very much for your help.
    OK
    Sub Rainbowchange()
    For x = 10 To 570
    For y = 57 To 68
    With Worksheets("Sheet1").Cells(x, y)
    Select Case .Value

    Case Is > 2
    .Interior.Color = RGB(11, 165, 11)

    Case Is > 1.6
    .Interior.Color = RGB(14, 214, 14)
    Case Is > 1.2
    .Interior.Color = RGB(58, 242, 58)
    Case Is > 0.8
    .Interior.Color = RGB(129, 247, 129)
    Case Is > 0.4
    .Interior.Color = RGB(199, 251, 199)
    Case Is > 0.2
    .Interior.Color = RGB(255, 255, 149)

    Case Is > 0
    .Interior.Color = RGB(255, 255, 195)
    Case Is = 0
    .Interior.Color = RGB(255, 255, 255)
    Case Is > -0.2
    .Interior.Color = RGB(252, 224, 224)
    Case Is > -0.4
    .Interior.Color = RGB(249, 189, 189)
    Case Is > -0.8
    .Interior.Color = RGB(245, 143, 143)
    Case Is > -1.2
    .Interior.Color = RGB(241, 101, 101)
    Case Is > -1.6
    .Interior.Color = RGB(238, 70, 70)
    Case Is > -2
    .Interior.Color = RGB(235, 27, 27)
    Case Is < -1.99
    .Interior.Color = RGB(254, 18, 18)
    End Select
    End With
    Next y
    Next x
    End Sub

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm assuming the values in these cells are changed via links to other cells?
    The easiest way to accomplish what you are trying to do might be to have direct control over the assigning of values to these cells.

    Create a procedure with your select case statements, but no looping, and pass the value to this procedure. Pass the value of the cell and the address of the cell to be formatted. In other words, format the cells in sheet1 from the change event in sheet2.

    Do you understand?

    You also could pass place your code, as is, in the worksheet calculate event and run your code everytime a cells value is changed
    via calculation.

    Try it

    Tom

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I found this code from a quick search of this board, guess whos code it was, the notorious TsTom It is a previous response to your exact question and uses the method he describes above

    i changed the rows and columns to what you wanted them to be.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Row > 9 And Target.Row < 570 And Target.Column > 56 And Target.Column < 69 Then
    With Target
    Select Case .Value

    Case Is > 2
    .Interior.Color = RGB(11, 165, 11)
    Case Is > 1.6
    .Interior.Color = RGB(14, 214, 14)
    Case Is > 1.2
    .Interior.Color = RGB(58, 242, 58)
    Case Is > 0.8
    .Interior.Color = RGB(129, 247, 129)
    Case Is > 0.4
    .Interior.Color = RGB(199, 251, 199)
    Case Is > 0.2
    .Interior.Color = RGB(255, 255, 149)

    Case Is > 0
    .Interior.Color = RGB(255, 255, 195)
    Case Is = 0
    .Interior.Color = RGB(255, 255, 255)
    Case Is > -0.2
    .Interior.Color = RGB(252, 224, 224)
    Case Is > -0.4
    .Interior.Color = RGB(249, 189, 189)
    Case Is > -0.8
    .Interior.Color = RGB(245, 143, 143)
    Case Is > -1.2
    .Interior.Color = RGB(241, 101, 101)
    Case Is > -1.6
    .Interior.Color = RGB(238, 70, 70)
    Case Is > -2
    .Interior.Color = RGB(235, 27, 27)
    Case Is < -1.99
    .Interior.Color = RGB(254, 18, 18)
    End Select
    End With
    End If

    End Sub



  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks very much for the ideas . I'll give them a go and see if I can understand what's going on .

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm afraid I can't follow this . My VB is not good and I had help with the original macro. I have modified it but am not able to develop my own. I'll try to clarify what I am doing.At the moment each worksheet is a separate entity and the cells within the range specified are deriving their value from cells outside the range but on the same worksheet.
    Each column within the range has a formula in it and all of the cells in the column have the same formula but different values depending on their data sources. At the moment I am changing the formulas constantly trying to develop the magic formula that does exactly what I want. Each time I alter a formula the values in all cells within the column change and so their colours need to change .The macro I was using works well but is incredibly slow. Can I use the modifications suggested using Target to create a macro that will work much more quickly than the one I have been using.Looks like I'll have to do a lot more work on VB.I've looked at the other conditional formatting topics but I can't find one doing what I am after. If there is one and I'm missing it could you please let me know which page or date it is on and under what search topic.
    Thanks very much for the efforts.
    OK

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Ken
    If you'd like, send me a copy of your sheet.
    TsTom@hotmail.com
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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