# Coloring of cells based on date within a range

#### vthokienj

##### Board Regular
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### etaf

##### Well-known Member
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

##### Well-known Member
correction to the formula

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

ABCDEFGHI
1
2Event 1category1
3Event 2category3event 1event 5
4Event 3category2event 3event 4
5Event 4category2event 2
6Event 5category1
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F3:I5Expression=COUNTIFS( \$A\$2:\$A\$6, F3, \$C\$2:\$C\$6, "category3" ) >0textNO
F3:I5Expression=COUNTIFS( \$A\$2:\$A\$6, F3, \$C\$2:\$C\$6, "category2" ) >0textNO
F3:I5Expression=COUNTIFS( \$A\$2:\$A\$6, F3, \$C\$2:\$C\$6, "category1" ) >0textNO

#### vthokienj

##### Board Regular
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

##### Well-known Member

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

#### vthokienj

##### Board Regular
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

##### Well-known Member

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

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

you are welcome

Replies
9
Views
113
Replies
0
Views
259
Replies
1
Views
344
Replies
3
Views
348
Replies
4
Views
438

1,148,364
Messages
5,746,276
Members
424,003
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back