Conditional Formatting Question. 3

[Hasit]

I am sure that there is a relatively straight forward answer to this, but I can't seem to get it.

The situation
[tt]
Start End Jan Feb Mar Apr May Jun Jul......Dec
1 5 O O O O O
3 4 O O
[/tt]
I need a formula that will take the start and end numbers and assign them to the months of the year and color in the cell in blue related to the start and end numbers (where 1=Jan and 12=Dec).

Any thoughts?

 

[GlennUK]

Hi Hasit,

select the data area under the months titles, and do Conditional Formatting using Formula Is with this formula ...

=AND(COLUMN()-2>=$A2,COLUMN()-2<=$B2)

assuming the active cell is on row 2, and the Start and End figures are in columns A and B, this should do what you want.

Cheers, Glenn.

 

[Zathras]

If the sheet is set up this way:
[blue]

A1: 'Start
B1: 'End
C1: 'Jan
D1: 'Feb
  (etc.)
A2: 1
B2: 5
A3: 3
B3: 4

[/color]
then you can select cell [blue]

 C2

[/color] and click Format / Conditional Formatting... from the menu. Change the first combo from &quot;Cell Value Is&quot; to &quot;Formula Is&quot; and enter the following formula:
[blue]

    =AND($A2<=CELL(&quot;col&quot;,A$1),$B2>=CELL(&quot;Col&quot;,A$1))

[/color]
Then click &quot;Format...&quot; and select the &quot;Patterns&quot; tab to set the color you want. Copy the formula from [blue]

 C2

[/color] into [blue]

 C2:N3

[/color].
Note that the formula is column-specific. If your actual month numbers are not in columns &quot;A&quot; and &quot;B&quot; then the formula needs to be adjusted.

 

[Loomah]

Hasit
On the assumption that this is the same file, you could use this formula as the dates shoud be in serial number format rather than text

=IF(AND(MONTH(C$1)>=$A$2,MONTH(C$1)<=$B$2),1,0)

You can then assign conditional formatting to the result of the formula. It might be worth looking at using blank and double blank if you don't want 1s and 0s all over the place.

If this is related to the same file I was helping with and I've over simplified your problem, please feel free to email me.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Hasit]

GlennUK: I couldn't get the formula to work. I am sure its my error, so I will check it again in a short while.

Zathras: I can't set up the data in the way you suggest. Its a report format, which is easier to read the way its been set up. Thanks for the suggestion though.

Loomah: It is the same file. I have set up (yet another!) worksheet and am trying to do a simple Gantt chart based on months resource is used. Conditional Formatting doesn't allow a reference to another worksheet/workbook. I then used a simple formula in the cell adding up the resource usage and in CF stated that if the cell was > 0.1 to shade the cell. Even if the cell was empty however, the cell shaded which confused the hell outta me.

I am going to check all the formulas from your 3 posts again.

 

[Hasit]

Loomah, GlennUK, I have the result I was after.

GlennUK, I can see what you were suggesting, and I have given you a star, because thats quite clever!

Loomah, you get a star simply because you came up with the answer I needed.

Thanks to all.

 

[Loomah]

Hasit, thanks!
I suppose it's possible to combine the answers and put this formula in the conditional formatting....

=AND(MONTH(C$1)>=$A2,MONTH(C$1)<=$B2)

which would allow for the months changing without having to have formulae in the cells.

I've given Zathras a star. Bizarrely because I didn't understand the formula so it made me go and find out and it's broadened my knowledge!!

Someone once told me that 90% of Excel users only use 10% of its functionality (possibly made up but that's statistics!!) Times like this and I'm still in the 90%!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?