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

Thread: conditional formatting revised

  1. #1
    Guest

    Default

    basic problem:
    I need to indicate progress in 6 steps. I came up with the following:

    A row contains 30 cells (each represents a particular phase of a development). I have 6 input fields to indicate the progrees at 6 point in time) . Upon first input (say 2) the first 2 cells of the row should turn red (the other 28 stay white. Upon the second input( say 6), the first 2 cells in the row should stay red, the next four should turn blue (or any other color) etc., etc. After 6 inputs all cells (or part of the total range) have a color.

    I got as far as 3 colors becouse Excel does not allow for more than 3 conditional formats. Manually coloring the required amount of cells is not an option since I need to do some 1500 rows every time. I am at a loss. Any suggestions??

    Pete

  2. #2
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    864
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    Once you get past the 3 conditions allowed in conditional formatting, you are pretty much stuck with writing a VBA procedure to apply the formatting.
    View a collection of recent Excel articles in the Excel Daily News

  3. #3
    Guest

    Default

    On 2002-03-08 05:00, MrExcel wrote:
    Once you get past the 3 conditions allowed in conditional formatting, you are pretty much stuck with writing a VBA procedure to apply the formatting.
    I am not familliar with VBA. VBA=Visual Basic A?. Would this be a difficult problem to solve in VBA for someone who has no knowlege of VBA?

    Pete

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Pete
    Would it be possible to deal with your 30 cells in two parts? For example if cell 15 always represents the end of your 3rd phase, you could use conditional formatting for cells 1-15 and different conditional formatting for cells 16-30.
    Just a thought
    regards
    Derek

  5. #5
    Guest

    Default

    On 2002-03-08 06:01, Derek wrote:
    Hi Pete
    Would it be possible to deal with your 30 cells in two parts? For example if cell 15 always represents the end of your 3rd phase, you could use conditional formatting for cells 1-15 and different conditional formatting for cells 16-30.
    Just a thought
    regards
    Derek
    Thanks Derek,

    I have thought about the same idea. To my disappointment the end of 3rd fase is not fixed. Therefore your suggestion is no good. THANKS anyway.

    Pete

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pete
    Is it really important that all 6 colours are different? I can do it with 3 colours which repeat, eg red,yellow,blue,red,yellow,blue
    Post if this is any good to you
    regards
    Derek

  7. #7
    Guest

    Default

    On 2002-03-08 07:10, Derek wrote:
    Pete
    Is it really important that all 6 colours are different? I can do it with 3 colours which repeat, eg red,yellow,blue,red,yellow,blue
    Post if this is any good to you
    regards
    Derek

    G'day Derek,

    This sounds too good to be true. Alternating colors would be fine!!! What is the secret???

    Thanks

    Pete

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pete
    Okay, hope you can follow this:

    For your row of 30 cells I used A1:AD1
    A3:F3 are 6 cells I used for input stages completed (eg A3 = 5, B3 = 10, C3 = 15 etc)
    Select A1:AD1 and use the following conditional formatting
    =OR(A1<=$A$3,AND(A1>$C$3,A1<=$D$3)) format cells green
    =OR(A1<=$B$3,AND(A1>$D$3,A1<=$E$3)) format cells yellow
    =OR(A1<=$C$3,AND(A1>$E$3,A1<=$F$3)) format cells pink

    This gives me bands of green,yellow,pink,green,yellow,pink

    Hope this works for you
    regards
    Derek
    PS Perhaps I should also have said that your progress row A1:AD1 contains the numbers in sequence 1,2,3,4 to 30 (I thought this was implied)



    [ This Message was edited by: Derek on 2002-03-08 22:52 ]

  9. #9
    Guest

    Default

    On 2002-03-08 07:52, Derek wrote:
    Pete
    Okay, hope you can follow this:

    For your row of 30 cells I used A1:AD1
    A3:F3 are 6 cells I used for input stages completed (eg A3 = 5, B3 = 10, C3 = 15 etc)
    Select A1:AD1 and use the following conditional formatting
    =OR(A1<=$A$3,AND(A1>$C$3,A1<=$D$3)) format cells green
    =OR(A1<=$B$3,AND(A1>$D$3,A1<=$E$3)) format cells yellow
    =OR(A1<=$C$3,AND(A1>$E$3,A1<=$F$3)) format cells pink

    This gives me bands of green,yellow,pink,green,yellow,pink

    Hope this works for you
    regards
    Derek
    PS Perhaps I should also have said that your progress row A1:AD1 contains the numbers in sequence 1,2,3,4 to 30 (I thought this was implied)


    Hello Derek,

    THANK YOU VERY MUCH! I took it for a test run and it works like clock work. My problems are over!!

    Regards,

    Pete

    [ This Message was edited by: Derek on 2002-03-08 22:52 ]

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
  •