Match multiple ranges and fill interseting cell color

[wizard4all]

Hello,

I would like to use vb to fill a cell with color when the intersection of multiple ranges matches, all being dates. Example, lookup range A1:B4 and B1:B4 for matches in C1:F1. If A is empty then it would still look to fill the one B date match.

http://imgur.com/2cpZD

I know I can do this with conditional formatting but i am unable to overwrite the color in that case. I will be loading a sheet with multiple dates and would like to just run a macro due to the volume of data I have and the need to overwrite after the initial macro run.

Appreciate any help!

Thank you!!

 

[Peter_SSs]

Welcome to the mrexcel board!

Assuming the dates are real dates and not just text, try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ColourCells()<br>    <SPAN style="color:#00007F">Dim</SPAN> lHeaderStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRowStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRowLength <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lAdate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rCel <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    lHeaderStart = Range("C1").Value<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rCel <SPAN style="color:#00007F">In</SPAN> Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)<br>        lAdate = IIf(rCel.Value = "", rCel.Offset(, 1).Value, rCel.Value)<br>        lRowStart = lAdate - lHeaderStart + 2<br>        lRowLength = rCel.Offset(, 1).Value - lAdate + 1<br>        rCel.Offset(, lRowStart).Resize(, lRowLength).Interior.ColorIndex = 3<br>    <SPAN style="color:#00007F">Next</SPAN> rCel<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Here's the results for me.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Start	End	28-Feb	1-Mar	2-Mar	3-Mar	4-Mar	5-Mar
2	28-Feb	1-Mar
3	1-Mar	3-Mar
4		4-Mar
5	1-Mar	4-Mar
Colour Cells

 

[wizard4all]

very much appreciated, this works great!