conditional formatting revised

Mar 8th, 2002, 05:14 AM

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

MrExcel · Mar 8th, 2002, 06:00 AM

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.

Mar 8th, 2002, 06:16 AM

Quote:

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

Derek · Mar 8th, 2002, 07:01 AM

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

Mar 8th, 2002, 07:07 AM

Quote:

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

Derek · Mar 8th, 2002, 08:10 AM

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

Mar 8th, 2002, 08:35 AM

Quote:

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

Derek · Mar 8th, 2002, 08:52 AM

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 ]

Mar 11th, 2002, 01:24 AM

Quote:

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 ]

Mar 8th, 2002, 06:00 AM	#2
MrExcel . Join Date: Feb 2002 Location: Akron, Ohio USA Posts: 789	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. __________________ Preview my latest book for Free

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 8th, 2002, 05:14 AM	#1
Guest Posts: n/a	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

Mar 8th, 2002, 07:01 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Mar 8th, 2002, 08:10 AM	#6
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Mar 8th, 2002, 08:52 AM	#8
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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 ]