Coloring of cells based on date within a range

vthokienj

Board Regular
Joined
Aug 1, 2011
Messages
101
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,874
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Oct 24, 2012
Messages
4,874
Office Version
  1. 365
Platform
  1. MacOS
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
 

vthokienj

Board Regular
Joined
Aug 1, 2011
Messages
101
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
Joined
Oct 24, 2012
Messages
4,874
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

vthokienj

Board Regular
Joined
Aug 1, 2011
Messages
101
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,874
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,874
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Forum statistics

Threads
1,141,284
Messages
5,705,510
Members
421,399
Latest member
hjweiss00

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
Top