Coloring of cells based on date within a range

vthokienj

Board Regular
Joined
Aug 1, 2011
Messages
105
Office Version
  1. 365
Platform
  1. Windows
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.

1624455750620.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
correction to the formula

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

Cell Address - ETAF.xlsx
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
 
Upvote 0
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.
 
Upvote 0
we can incorporate dates, just was not sure what you wanted -
 
Upvote 0
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.

1624562337620.png
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top