conditional formatting revised

G

Guest

Guest
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top