Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colourcoding range between two values
Hi,
In the range C3:BC277 I have an arrayformula. In each row there will always be one cell with the value 1 and sometimes a cell with the value 2. 2 will always be to the right of 1. The sheet is basically a gantt chart, so I would like to colour the cells with values 1 or 2 - this is easy enough, but colouring the cells between the 1 and the 2 is giving me some trouble. I cannot use xlToRight since all cells contains a formula, so what to do? Any pointers? /Sune |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colourcoding range between two values
Try this as the third conditional format with CF's (first CF for value 1 &
second for value 2) =AND(SUM($C3:C3)=1,SUM(D3:$BC3) = 2) When entering this ensure C3 is the selected cell. Copy and paste-special formats to your range C3:BC277 Depending on what you need you might try Count instead of Sum or something else. Regards, Peter T "Sune Fibaek" wrote in message ... Hi, In the range C3:BC277 I have an arrayformula. In each row there will always be one cell with the value 1 and sometimes a cell with the value 2. 2 will always be to the right of 1. The sheet is basically a gantt chart, so I would like to colour the cells with values 1 or 2 - this is easy enough, but colouring the cells between the 1 and the 2 is giving me some trouble. I cannot use xlToRight since all cells contains a formula, so what to do? Any pointers? /Sune |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colourcoding range between two values
Peter,
That does exactly what I wanted it to! I almost had a VBA solution, but that is about 50 lines of quite badly written code and obviously must be run whenever the range changes and it has just been scrapped. Thanks loads, /Sune "Peter T" wrote: Try this as the third conditional format with CF's (first CF for value 1 & second for value 2) =AND(SUM($C3:C3)=1,SUM(D3:$BC3) = 2) When entering this ensure C3 is the selected cell. Copy and paste-special formats to your range C3:BC277 Depending on what you need you might try Count instead of Sum or something else. Regards, Peter T "Sune Fibaek" wrote in message ... Hi, In the range C3:BC277 I have an arrayformula. In each row there will always be one cell with the value 1 and sometimes a cell with the value 2. 2 will always be to the right of 1. The sheet is basically a gantt chart, so I would like to colour the cells with values 1 or 2 - this is easy enough, but colouring the cells between the 1 and the 2 is giving me some trouble. I cannot use xlToRight since all cells contains a formula, so what to do? Any pointers? /Sune |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
Sum values in columns based on values in named range | Excel Worksheet Functions | |||
Search/Filter to find values in another range based on two cell values | Excel Programming |