Coloring of cells based on date within a range

[vthokienj]

I want to assign a color to a cell based on there being an event that falls within a specific date range. As shown below, event1, falling within the week of July 4,
would cause F3 to be blue, since it has category1. So the cells in range F3:I5 would be colored based on a running list of events.

I've looked at some of the conditional formatting but can't seem to come up with a solution to make this work. Any help would be greatly appreciated.
Thanks in advance.

 

[etaf]

should be able to use a COUNTIFS or lookup
with a RULE for each colour

Not sure about the date though

anyway
Select F3:I5

Then a rule

=COUNTFS( $A$2:$C$6 , F3, $C$2:$C$6 , "category 1" ) >0

repeat for 2 other colours
=COUNTFS( $A$2:$C$6 , F3, $C$2:$C$6 , "category 2" ) >0
=COUNTFS( $A$2:$C$6 , F3, $C$2:$C$6 , "category 3" ) >0


i'll make a spreadsheet and post back

 

[etaf]

correction to the formula

=COUNTIFS( $A$2:$C$6 , F3, $C$2:$C$6 , "category 1" ) >0

Cell Address - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1
2	Event 1		category1
3	Event 2		category3			event 1			event 5
4	Event 3		category2				event 3	event 4
5	Event 4		category2				event 2
6	Event 5		category1
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:I5	Expression	=COUNTIFS( $A$2:$A$6, F3, $C$2:$C$6, "category3" ) >0	text	NO
F3:I5	Expression	=COUNTIFS( $A$2:$A$6, F3, $C$2:$C$6, "category2" ) >0	text	NO
F3:I5	Expression	=COUNTIFS( $A$2:$A$6, F3, $C$2:$C$6, "category1" ) >0	text	NO

 

[vthokienj]

Appreciate the quick response, this definitely gets me close. I hope to turn this into something with the dates and when I get that going, I'll post back here. Thanks again.

 

[etaf]

we can incorporate dates, just was not sure what you wanted -

 

[vthokienj]

The date - events will continually be added to columns A/B/C. I just want to 'activate' the correct cell when one is added.
So, if a new event 6 is added for 7/4 with category 3, then cell F5 should become yellow.

 

[etaf]

just extend the range for the countifs in the conditional formatting rules
600 , 1000 whatever rows you want to include

=COUNTIFS( $A$2:$C$600 , F3, $C$2:$C$600 , "category 1" ) >0

And also extend the conditional format range F2 to I?? whatever rows - OR F2: ?? Columns

 

[vthokienj]

So you do. Thanks etaf for the quick response and solution.

 

[etaf]

you are welcome