Conditional Formatting every other occurence

tahonick

New Member
Joined
Feb 17, 2016
Messages
10
Hi all,

Background
I'm creating a sick/vacation tracker. I want the dates (and days of the week) going horizontally across the spreadsheet. It automatically sums sick and vacation this way easily.

The Problem

I have to enter these days into payroll every other Thursday, so I want to have every other Thursday highlighted red using conditional formatting, as I have it setup to change the date labels (Mon, Tues, etc.) to automatically update based on the year listed in another cell. How would I do have it automatically highlight every other Thursday?

It currently looks like this:
rdsAlEQ.png


I want it to look like this:
U4v6Gx0.png


Thanks in advance!

Best,
Tahonick
 
Ok...
Second one first.... change M6 to M$6 in both or conditions... That will highlight down rows...

The first one is trickier... I think you need to try and find out which part of the formula is failing... To do this, copy the formula in the formatting and put it in cell M7 (below the formatted stuff) then, using the fill handle drag it to the right.
This should give you trues and falses in each cell... you can then use evaluate formula on the cells you think it should be working on and see where the formula is going wrong.

Good luck,

CN.
 
Upvote 0
Woohooo!!! Solved it!

It should have been: =AND(MOD(COLUMNS($M:M),14)>=7,M6="Thu")

That took me way too long to figure out. I didn't understand how mod worked going into it. Glad I finally figured it out though. It's beautiful.

Many thanks again!
 
Upvote 0

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