Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Formula to apply conditional formatting to highlight specific cells based the cell to the left


I would want to highlight cells on each Friday of the week for those teams in the same order as shown in the spreadsheet below. The cells colored red with text "Fun Time" should be applied as and when the conditional formatting is set to a formula.

Is it possible through conditional formatting alone?



Selected Answer

Dr Liss

Not sure I understand your question but conditional formatting can be used to highlight each Friday for the 5 teams in sequence.

In the attached file, I've added a new rule (based on a formula) to cell B3:

=IF(MOD(COLUMN()-2 - (ROW()-3)*7, 35)=0,1,0)
where the subtracted numbers make B3 in position 0,0 and the 7 makes a difference of 7 (days) between a team and the one in the row above. The 35 (7*5) is the divisor for the MOD worksheet function and gives the TRUE result once every 5 eeeks per team. 

I've then changed the "applies to" to  $B$3:$BG$7 (and added some extra date headings to show it loops around so the first team get "Fun Time" after team 5).

Hope this is what you need.



Hi John, Yes the formula is working perfectly if I start from B3. But when I start from AO3 it starts to highlight all mondays. I understand I need to modify the formula a bit to accomodate changes. Can you please help me understand the formula as how did you determine 35 is divisor?
=IF(MOD(COLUMN()-2 - (ROW()-3)*7, 35)=0,1,0)
Dr Liss (rep: 26) Jun 29, '22 at 6:29 am
35 is 5 teams times 7 days (as I said when I revised my answer). I'm out so can't check anything, sorry. 
John_Ru (rep: 5572) Jun 29, '22 at 7:13 am
If you start from AO3 (in a Tuesday column for me), you need to take 44 off COLUMN() rather than 2 - that will make cell AR3 the first red cell, so
=IF(MOD(COLUMN()-44 - (ROW()-3)*7,35)=0,1,0)
But that's not what your question asked...
John_Ru (rep: 5572) Jun 29, '22 at 7:16 am
Did you try my corrected suggestion?
John_Ru (rep: 5572) Jun 29, '22 at 7:56 am
Got it! Since I had some other data in the biginning cells from A-Z, I had to push these to AO
If I would like to add text say "Fun Time" to those cells highlighted in red. Can it be done using conditional formatting?
Dr Liss (rep: 26) Jun 29, '22 at 9:53 am
Again I'm mobile so can't check on desktop Excel but I'm pretty sure that you can't do that with conditional formating alone. 

You could put this formula in AO3:

=IF(MOD(COLUMN()-44 - (ROW()-3)*7, 35)=0,"FunTime" ,"" )

then copy it down then right. You'll then have the text plus the conditional fill.
John_Ru (rep: 5572) Jun 29, '22 at 10:24 am
Just corrected that to read 44 for AO3 start.

Hope that fixes things I have nothing to add (since you wanted to avoid VBA)
John_Ru (rep: 5572) Jun 29, '22 at 10:28 am
P.s. if you use the formula in your cells, you can simplify your conditional formatting formula to just look for "Fun Time" in the cells. No need to repeat the ROWS and COLUMNS formula.
John_Ru (rep: 5572) Jun 30, '22 at 3:21 am
Thanks John. That was a good idea and it worked.
Thanks you so much for your help:)
Dr Liss (rep: 26) Jun 30, '22 at 3:59 am
Happy to help. Thanks for selecting my answer, Dr Liss. 
John_Ru (rep: 5572) Jun 30, '22 at 5:22 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login