Excel 2003 VBA or conditional formatting?

[Duffgen]

Hello everybody,

Below is an example of a annual roster on which I'm working. I've only shown a small area, for brevity.

The coloured areas are conditionally formatted. Ivory shows Away, green is for Work, pale blue is a day on which a shift-change may occur, and TD is a Transition Day. I've not shown the conditional formatting formulae simply because the page then gets too large for easy viewing . The mustard colour is the "base", so I don't need to use up a CF option for those cell-values.

What I'd like to do is collect the date from a cell outside the formatted area, rows 54 and 55 in the extract, and then colour the relevant cell red, as per the first example. ie AB has an Expiry date of the 4th Jan, therefore fill the cell corresponding to the relevant day in red. BD52 should colour to indicate CD's expiry.

Is it possible to do this with a CF formula, or is VBA the answer?

Many thanks in advance for any suggestions on the solution.

Roster

*	BA	BB	BC	BD	BE	BF	BG	BH
43	2012	*	01	02	03	04	05	06
44	*	Jan	Sun	Mon	Tue	Wed	Thu	Fri
45	*	AB	X	X	X	X	X	X
46	*	CD	1	1	1	1	1	1
47	*	Feb	Wed	Thu	Fri	Sat	Sun	Mon
48	*	AB	1	1	1	1	1	X
49	*	CD	X	X	X	X	TD	1
50	*	Mar	Thu	Fri	Sat	Sun	Mon	Tue
51	*	AB	1	1	1	1	1	1
52	*	CD	X	X	X	X	X	X
53	*	*	*	*	*	*	*	*
54	*	AB	Expiry	04-Jan	*	*	*	*
55	*	CD	Expiry	02-Mar	*	*	*	*

<tbody>
</tbody>

Spreadsheet Formulae

Cell Formula BB43 =DATE($BA$43,1,1) BC43 =DATE($BA$43,1,1) BD43 =DATE($BA$43,1,2) BE43 =DATE($BA$43,1,3) BF43 =DATE($BA$43,1,4) BG43 =DATE($BA$43,1,5) BH43 =DATE($BA$43,1,6) BC44 =DATE($BA$43,1,1) BD44 =DATE($BA$43,1,2) BE44 =DATE($BA$43,1,3) BF44 =DATE($BA$43,1,4) BG44 =DATE($BA$43,1,5) BH44 =DATE($BA$43,1,6) BC46 =IF(BC45>0,IF(AND(BC45=1,BD45="X"),"TD",IF(BC45=1,"X",IF(BC45="X",1,IF(BC45="TD",1,0)))),0) BD46 =IF(AND(BD45=1,BE45="X"),"TD",IF(BD45=1,"X",IF(BD45="X",1,IF(BD45="TD",1,0)))) BE46 =IF(AND(BE45=1,BF45="X"),"TD",IF(BE45=1,"X",IF(BE45="X",1,IF(BE45="TD",1,0)))) BF46 =IF(AND(BF45=1,BG45="X"),"TD",IF(BF45=1,"X",IF(BF45="X",1,IF(BF45="TD",1,0)))) BG46 =IF(AND(BG45=1,BH45="X"),"TD",IF(BG45=1,"X",IF(BG45="X",1,IF(BG45="TD",1,0)))) BH46 =IF(AND(BH45=1,BI45="X"),"TD",IF(BH45=1,"X",IF(BH45="X",1,IF(BH45="TD",1,0)))) BB47 =DATE($A$1,2,1) BC47 =DATE($BA$43,2,1) BD47 =DATE($BA$43,2,2) BE47 =DATE($BA$43,2,3) BF47 =DATE($BA$43,2,4) BG47 =DATE($BA$43,2,5) BH47 =DATE($BA$43,2,6) BC49 =IF(BC48>0,IF(AND(BC48=1,BD48="X"),"TD",IF(BC48=1,"X",IF(BC48="X",1,IF(BC48="TD",1,0)))),0) BD49 =IF(AND(BD48=1,BE48="X"),"TD",IF(BD48=1,"X",IF(BD48="X",1,IF(BD48="TD",1,0)))) BE49 =IF(AND(BE48=1,BF48="X"),"TD",IF(BE48=1,"X",IF(BE48="X",1,IF(BE48="TD",1,0)))) BF49 =IF(AND(BF48=1,BG48="X"),"TD",IF(BF48=1,"X",IF(BF48="X",1,IF(BF48="TD",1,0)))) BG49 =IF(AND(BG48=1,BH48="X"),"TD",IF(BG48=1,"X",IF(BG48="X",1,IF(BG48="TD",1,0)))) BH49 =IF(AND(BH48=1,BI48="X"),"TD",IF(BH48=1,"X",IF(BH48="X",1,IF(BH48="TD",1,0)))) BB50 =DATE($A$1,3,1) BC50 =DATE($BA$43,3,1) BD50 =DATE($BA$43,3,2) BE50 =DATE($BA$43,3,3) BF50 =DATE($BA$43,3,4) BG50 =DATE($BA$43,3,5) BH50 =DATE($BA$43,3,6) BC52 =IF(BC51>0,IF(AND(BC51=1,BD51="X"),"TD",IF(BC51=1,"X",IF(BC51="X",1,IF(BC51="TD",1,0)))),0) BD52 =IF(AND(BD51=1,BE51="X"),"TD",IF(BD51=1,"X",IF(BD51="X",1,IF(BD51="TD",1,0)))) BE52 =IF(AND(BE51=1,BF51="X"),"TD",IF(BE51=1,"X",IF(BE51="X",1,IF(BE51="TD",1,0)))) BF52 =IF(AND(BF51=1,BG51="X"),"TD",IF(BF51=1,"X",IF(BF51="X",1,IF(BF51="TD",1,0)))) BG52 =IF(AND(BG51=1,BH51="X"),"TD",IF(BG51=1,"X",IF(BG51="X",1,IF(BG51="TD",1,0)))) BH52 =IF(AND(BH51=1,BI51="X"),"TD",IF(BH51=1,"X",IF(BH51="X",1,IF(BH51="TD",1,0)))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



<!-- ######### End Created Html Code To Copy ########## -->

 

[ukmikeb]

Hi

Identical post :-
Excel 2003 VBA or conditional formatting. Date in one cell needs to format remote cell

If you cross-post please quote the post in your thread on the other forum.

Thank you.

 

[ukmikeb]

Hi

I managed to satisfy the Conditional Formatting with a bit of a workaround.

Here is the formatted part of the worksheet :-
June

	BA	BB	BC	BD	BE	BF	BG	BH
43	2012	*	1	2	3	4	5	6
44	*	Jan	Sun	Mon	Tue	Wed	Thu	Fri
45	40912	AB	X	X	X	X	X	X
46	40970	CD	1	1	1	1	1	1
47		Feb	Wed	Thu	Fri	Sat	Sun	Mon
48	40912	AB	1	1	1	1	1	X
49	40970	CD	X	X	X	X	TD	1
50		Mar	Thu	Fri	Sat	Sun	Mon	Tue
51	40912	AB	1	1	1	1	1	1
52	40970	CD	X	X	X	X	X	X

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula BA45 =IFERROR(VLOOKUP($BB45,$BB$54:$BD$55,3,FALSE),"") BA46 =IFERROR(VLOOKUP($BB46,$BB$54:$BD$55,3,FALSE),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

And here is the helper table (part) :-
June

	CI	CJ	CK	CL
45	0	0	0	1
46	0	0	0	0
47	0	0	0	0
48	0	0	0	0
49	0	0	0	0
50	0	0	0	0
51	0	0	0	0
52	0	1	0	0

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula CI45 =--(IFERROR(MATCH($BA45,OFFSET($BC45:$BH45,-(MOD(ROW(),3)+1),),0)=COLUMNS($CI:CI),)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Dragged across for 31 columns and down as required.

In order to construct the helper table I have created the Expiry Date for each code in the column adjacent to the code (BA) for each month.

Then, the formula for conditional formatting for the whole block (BC45 to BH52 in the example) is :-

=AND(MOD(ROW(),3)<2,OFFSET(BC45,,32)=1)

and apply the colour fill as required.

I was almost on the verge of suggesting a solution where the dates ran down the columns and the codes across the columns with the Expiry date positioned one row below the code.

hth

 

[ukmikeb]

Hi

Removing the necessity for the capture of the appropriate dates in Column BA use this formula in CI45 :-

=--(IFERROR(MATCH(VLOOKUP($BB45,$BB$54:$BD$55,3),OFFSET($BC45:$CG45,-(MOD(ROW(),3)+1),),0)=COLUMNS($CI:CI),FALSE))

dragged across to cell DO45 and then down.

hth